Forum Discussion
DateDiff with Working Days and Sums
I am currently transforming old reports to new PowerBI ones and am stuck on this particular part. I need to do a date difference beween DepositDate and CmplDate, this needs to be for working days only and needs to return with a number of the differences. Then i also need to have a conditional title for these where if there are more than 10 working days text will return to say "More than 10 working days", and same again for less than 10.
I also need to have a sum to show the amount of days (i am assuming based off of the information on the old reports).
Please note that the CmplDate also has quite a lot of blanks and therefore i need to have a filter on the page to show the information when the CmplDate is not blank. I do not know if this will mess with the potential measure or conditional columns (if these are the answer).
Below is the information from the old reports:
Date difference (working days) (TITLE IS "Deposit-PlanChk")
Less than 10
More than 10
Then need to Sum both of these
And then need to create a rule to title these?
So that they will appear on the table with this information
3 Replies
You would want to relate your data table to a Date Dimension. The Date Dimension table will have a row for every date, no gaps. One of the columns in the Date Dimension table would be WorkDay (values Y or No, 0 or 1, etc.) You can then use a CALCULATE function on the measure to FILTER only for WorkDay = 'Y'
I am so sorry i am really new at PowerBI and i dont understand what you mean by a Date Dimension table? Is this something that i will need to do in Power Query? If so, how would i do this?
So, you would need to learn about modeling in Power BI - that is a big subject.
Here are some good resources about Date Dimension.
Create a Date Dimension in Power BI in 4 Steps - Step 1: Calendar Columns - RADACAD
Create date tables in Power BI Desktop - Power BI | Microsoft Learn
How to Create New Dimension Date Table in Power BI - Zebra BI
