Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all,
I'm creating a dashboard in Power BI where currently, the data comes from a single Excel file, refreshed completely once a week (though all column headers remain the same).
With one table labelled 'Data', I've added a measure being elapsedDays = DATEIFF ( [Interaction_Start_Date],TODAY() , DAY).
This works perfectly in the Date Hierarchy (Year/Quarter/Month/Day) format, as this returns the days from the earliest start date and todays date correct, no matter what they may be/change to.
I've attempted to create a measure in a separate table to return the same information, though I break the data in the dashboard by doing so (a table with 12 months as the column headers turns to hundreds of days).
As I'm not that great with DAX etc yet, I've searched other various other posts/suggestions and failed so far.
Any help on how to fix this would be much appreciated.
Thanks
Hi @Mark_BI
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
My file is quite large, so I've made a written example below.
Note: It's originally listed as text then converted to a table. The bold are the headers.
Activity_Start_date | Activity_End_Date | Activity_Type | Status |
1/01/2021 | 1/01/2021 | Type 1 | Closed |
1/01/2021 | 5/04/2022 | Type 2 | Closed |
1/06/2022 | 1/06/2022 | Type 3 | Closed |
1/06/2022 | 1/06/2022 | Type 4 | Closed |
1/06/2022 | 1/06/2022 | Type 5 | Closed |
1/06/2023 | 1/07/2023 | Type 5 | Closed |
1/06/2023 | Type 4 | In Progress | |
1/10/2023 | Type 1 | Unallocated | |
1/10/2023 | 2/10/2023 | Type 3 | Closed |
Once in Power BI, what I did was create the table.
Rows - Activity_Type
Columns - Activity_Start_Date
Month
Values - Count of Activity_Type
It would show as below:
Activity Type | January | February | March | April | May | June | July | August | September | October | November | December | Total |
Activity 1 | 66 | 58 | 5 | 47 | 23 | 36 | 63 | 2 | 49 | 83 | 63 | 66 | 561 |
Activity 2 | 0 | 6 | 7 | 2 | 8 | 3 | 1 | 6 | 9 | 9 | 9 | 0 | 60 |
Activity 3 | 6 | 32 | 1 | 6 | 3 | 9 | 0 | 10 | 9 | 3 | 5 | 0 | 84 |
Activity 4 | 14 | 25 | 50 | 39 | 79 | 50 | 13 | 28 | 12 | 9 | 43 | 12 | 374 |
Activity 5 | 45 | 91 | 12 | 40 | 18 | 94 | 91 | 19 | 40 | 26 | 54 | 30 | 560 |
Total | 131 | 212 | 75 | 134 | 131 | 192 | 168 | 65 | 119 | 130 | 174 | 108 | 1639 |
I then added the DAX measure in Power BI-
This then created a useable filter where I could show/filter open and aged activities i.e. any 'Activity_End_Date' where there is no date is an activity that is considered 'Open'.
The 'Data' table works absolutely perfect with the elapsedDays measure input.
When creating a new 'Measure' table, it can't link up the relationship without causing the issues.
Hopefully this clears up what I'm trying to achieve, please let me know if I've missed anything.
Is that supposed to be "DATEDIFF" not "DATEIFF" ? Also the DAX looks to be missing an end bracket?
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |