Calculate number of weekdays between two dates

[Anonymous]

Hi,

I have a fact table F_TASKS that contains a foreign key (FK_DATE_START) that links to PK_DATE in a dim date table (D_DATE_START) representing a start date and a second foreig key (FK_DATE_STOP) to a copy of the first dim date table (D_DATE_STOP = D_DATE_START)  representing a stop date

The dim date tables (D_DATE_START and D_DATE_STOP) have a column IS_WEEKDAY (boolean) which indicates if a particular date is a weekdag (Mondag till Friday) or not a weekday (Saturday or Sunday).

For every record in F_TASKS I want to calculate the amount of workingdays between start date and the stop date, so the dates with IS_WEEKDAY = FALSE() are not taken in account, only the dates where IS_WEEKDAY = TRUE()
 

How can that be done?

R.W.

[v-piga-msft]

Hi @Anonymous ,

It seems that you have three tables in your scenario and have the relationships among them.

If it is convenient, could you please share the data sample which could reproduce your scenario and your desired output so that we could help further on it.

Best  Regards,

Cherry

[Anonymous · Accepted solution]

Hi,
I solved the problem by using a calculated column PROCESSING_TIME in the fact table F_TASKS
The idea is not to base the sollution on the link between the fact table F_TASK en de dim table D_DATE, but on filtering out the records in the dim table D_DATE using the two dates and the boolean that indicates if a date is a weekday and counting them

PROCESSING_TIME=

CALCULATE( COUNTROWS(D_DATE);

FILTER(D_DATE;D_DATE[DATE]>=F_TASKS[DATE_START]);

FILTER(D_DATE;D_DATE[DATE]<=F_TASKS[DATE_STOP]);

FILTER(D_DATE;D_DATE[IS_WEEKDAY] = TRUE()))