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:

• Date Table Tutorial

Hi @Christian Hauggaard , Many thanks for responding so swiftly. I am familiar with this date table. This is where I have entered our vacations. What I need now is that the ProductionDays only counts when the DayCategory = Production. This is needed when I create a visual in PowerBI. There I want to display my x number of production days forecast. The relative filter function by date then just does not work satisfactorily.

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 @daniel 

Thank you very much for the advice regarding calendar. 

My question is slightly different indeed. In my search today, I almost thought I had the solution with the Measure in PowerBI, but NETWORKDAYS does not work when it is not its own table within PowerBI. Even with the Custom Measure within TX, I have not yet succeeded. Below is the image of what I want to achieve. This should make it possible for me to include a range of number of production days in my reports. 

Is NONCLUSTERED INDEX in sql an option for this?

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 @daniel ,

To do a SUM based on IsWorkingDays I understand. But in my opinion not directly what I am looking for. In that, I realize well that I cannot express my question clearly enough. 

Have still looked at my question with a colleague, he is our SQL expert. He has written this out for me. This is what should be able to give me the result. I just don't know if and how I can work it into a Custom Field.

SELECT CalenderDate,
(SELECT COUNT(*) FROM Calendar S1
WHERE S1.IsProductive = 1
AND S1.CalendarDate BETWEEN GETDATE() AND T1.CalenderDate) AS ProductiveDaysOffset
FROM Calendar T1

I am looking forward to it. 

Regards, Stefan

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

Hi @Stefan Kauffeld 

Did you manage to resolve the issue? If so please help us by marking a best answer above. If you have any follow up questions please let us know

Hi @Stefan Kauffeld did you manage to test yet?