I am trying to get the date difference between Load Date and Plan Date but I need to exclude Saturdays and Sundays. Also if the Plan Date is blank, I need today's date + 1 day to replace the blank Plan Date and from there compute the date difference.
Can this be done in DAX? Appreciate if you can help me to create DAX formula as per my above requirements. Thanks!
Have a look at this:
Proud to be a Datanaut!
You may create a calendar table and use below measure.Attached the sample file for reference.
Measure = CALCULATE ( COUNT ( 'Calendar'[Date] ), FILTER ( GENERATE ( Table1, FILTER ( 'Calendar', 'Calendar'[ISWeekum] = 0 ) ), Table1[Load Date] < 'Calendar'[Date] && Table1[Plan Date2] >= 'Calendar'[Date] ) )
Hi @v-cherch-msft (Cherie),
Thank you for sending your suggestion. Unfortunately I cannot creat a calendar table since I am using a Direct Query for storage method. Is there any other way to do this without adding a calendar table?
Try below measure:
Measure 2 = var _Calendar=ADDCOLUMNS(CALENDAR(DATE(2019,1,1),DATE(2019,3,1)),"ISWeekum",IF(WEEKDAY([Date],1)>=6,1,0)) return CALCULATE ( COUNTROWS( FILTER ( GENERATE (Table1,FILTER(_Calendar,[ISWeekum]=0) ), Table1[Load Date] < [Date] && Table1[Plan Date2] >= [Date] ) ) )