Forum Discussion
dax measure
how to calculate number of days worked and number of days not worked from invoice date in powerbi
Hi nesrinehal20 ,
Regarding your question, if you are trying to calculate the number of days 'Monday through Friday' from the invoice date until the end of today, (excluding holidays), you can use the 'Weekday' function
WEEKDAY function (DAX) - DAX | Microsoft Learn
Use the following DAX expression to create measures
WorkDay = VAR _table = ADDCOLUMNS(CALENDAR(MAX('Table'[Date]),TODAY()),"DayNumberINAWeek",WEEKDAY([Date],2)) RETURN COUNTROWS(FILTER(_table,[DayNumberINAWeek] < 6 && NOT [Date] IN VALUES(Holidays[Date])))NoWorkDay = VAR _table = CALENDAR(MAX('Table'[Date]),TODAY()) RETURN COUNTROWS(_table) - [WorkDay]Final output
Best Regards,
Wenbin Zhou
1 Reply
Hi nesrinehal20 ,
Regarding your question, if you are trying to calculate the number of days 'Monday through Friday' from the invoice date until the end of today, (excluding holidays), you can use the 'Weekday' function
WEEKDAY function (DAX) - DAX | Microsoft Learn
Use the following DAX expression to create measures
WorkDay = VAR _table = ADDCOLUMNS(CALENDAR(MAX('Table'[Date]),TODAY()),"DayNumberINAWeek",WEEKDAY([Date],2)) RETURN COUNTROWS(FILTER(_table,[DayNumberINAWeek] < 6 && NOT [Date] IN VALUES(Holidays[Date])))NoWorkDay = VAR _table = CALENDAR(MAX('Table'[Date]),TODAY()) RETURN COUNTROWS(_table) - [WorkDay]Final output
Best Regards,
Wenbin Zhou