Creating an row_number/Index/Sequence based on working days excluding holidays

Hi @Stefan Kauffeld 

I would encourage you to use the native date table in TimeXtender

This allows you to add custom periods (e.g. holidays)

Please see the following article for more information:

Dear @Stefan Kauffeld ,

The feature Christian mentions and you use is ver nice, but do think about how you are going to keep that data up date. Now you have to import (eventough it is only once a year) the data into your TX enviroments to keep the data up to date.

You could consider loading straight from the Excel table and creating lookups to get the same result. A bit more work, but now (someone else) can maintain this file and keep it updated. Maybe load the data from a sharepoint list. (this is also very convenient for lists that update more often, like some custom filtering or custom names or different categorisation which is not in the source system. Otherwise the addind of this data needs to be done by the BI team and is now owner of this proces)

Hopefully I understand you correctly. What I would do is create a boolean field (1 or 0) from the DayCategory Filter where if the value = Production (column name = isProductionDay) you get a 1 otherwise you get a 0.
In PowerBI you can use a calculate with a filter to get rid of the weekends and holidays:
CALCULATE(
[Your calculation here]
,
isProductionDay = 1
)
I like to use booleans because performance wise they work better. But you can also set the filter here to  DayCategory = 'Production’

And in PowerBI on the date you can put a filter that is set to 7 days ahead or 30 days ahead.
If you dont want to create the boolean, you can let PowerBI filter the object to DayCategory = Production?

Hope this helps
= Daniel
 

Dear @Stefan Kauffeld ,
A NonClustered Index is a thing that TX can do to create a type of indexing on the SQL table. I dont think that it is going to be the solution.

Can't you use the Calculate option in your Expression? The Networkdays is great, but I will not deal with our Dutch holidays. besides, when counting something in your Dimension, the calculation will not change as the cardinality and filters of the dimension usually is from dimension to fact (you can try to set the filter option to both ways).

The way I use to count days from the calendar usually is give every day a 1 (create a column with fixed value 1). If I then need to take out weekends and holidays, I create a new column (isWorkingDays) with 2 fixed values  0 and 1 (zero first, as there are some conditions on this one). Then set the condition to DayCategory <> Working Day. So now i have a column with only a 1 when it is a working / production day. Then just use SUM(isWorkingDays) and I should count all the working days.

= Daniel

Hi Stefan

In your case I would put your query into a stored procedure, and convert it to an update statement on your custom field on your calendar table.

Then execute your stored procedure through a custom script set as a post data cleansing step on your calendar table

https://legacysupport.timextender.com/hc/en-us/articles/360051848111-Scripting