Forum Discussion
Difference day over day - column chart
Dear All,
I have a table with number of tickets per day and I would like to create a column chart that presents the difference from the precious day. For example, if on 22.01.2025 there are 15 tickets and on 21.01.2025 there were 10 tickets, I would like to see 5 tickets on the chart under 22.01.2025.
I tried this solution: https://stackoverflow.com/questions/40903902/dax-query-on-day-over-day but I got stuck with the first measure as I saw the error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist".
The raw data is in the format as in the table at the bottom.
Could you please advise on this topic?
Thank you in advance!
| Ticket id | Report date |
| 340569 | 22.01.2025 |
| 331642 | 22.01.2025 |
| 347834 | 22.01.2025 |
| 380187 | 22.01.2025 |
| 379780 | 22.01.2025 |
| 221161 | 21.01.2025 |
| 303183 | 21.01.2025 |
| 363956 | 21.01.2025 |
| 375567 | 21.01.2025 |
| 221161 | 20.01.2025 |
| 303183 | 20.01.2025 |
| 363956 | 20.01.2025 |
| 221252 | 20.01.2025 |
| 185276 | 20.01.2025 |
| 320844 | 20.01.2025 |
| 1001 | 17.01.2025 |
| 1001 | 17.01.2025 |
| 1001 | 17.01.2025 |
| 1001 | 17.01.2025 |
Hi, Anonymous
Thanks for Idrissshatila's reply. You can try his reply, or you can try the following dax.Table 2 = DISTINCT( SELECTCOLUMNS( 'Table', "Report Date",'Table'[Report date], "Counts",'Table'[Column] ) ) Index = RANKX(ALL('Table'),VALUE(FORMAT([Report Date], "YYYYMMDD")),,ASC,Dense) Diff = VAR _PreviousCounts = CALCULATE( MAX('Table 2'[Counts]), ALL('Table 2'), 'Table 2'[Index] = EARLIER('Table 2'[Index]) -1 ) RETURN IF(_PreviousCounts<>BLANK(),'Table 2'[Counts] - _PreviousCounts)Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
3 Replies
Hi, Anonymous
Thanks for Idrissshatila's reply. You can try his reply, or you can try the following dax.Table 2 = DISTINCT( SELECTCOLUMNS( 'Table', "Report Date",'Table'[Report date], "Counts",'Table'[Column] ) ) Index = RANKX(ALL('Table'),VALUE(FORMAT([Report Date], "YYYYMMDD")),,ASC,Dense) Diff = VAR _PreviousCounts = CALCULATE( MAX('Table 2'[Counts]), ALL('Table 2'), 'Table 2'[Index] = EARLIER('Table 2'[Index]) -1 ) RETURN IF(_PreviousCounts<>BLANK(),'Table 2'[Counts] - _PreviousCounts)Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hello Anonymous ,
you can check this function https://youtu.be/aW2NSyoL-Jg?si=6bqM1uYpRgO6Pn7z
and check this https://youtu.be/Ptof9AKz9eA?si=_X8Q3QOOlU3Un-10
Hello Idrissshatila ,
Thank you for the suggestions.
I created two measures according to the video:
YT Previous day = CALCULATE(DISTINCTCOUNT(Append1[Issue id]),DATEADD(Append1[Report date].[Date],-1,DAY))DoD Change = DISTINCTCOUNT(Append1[Issue id])-[YT Previous day]But as I don't have data for each day, the measure also shows the difference for the dates that aren't in my table.In the first column, I have the summary of my raw data. My goal would be to see -1 on 20.01. as the number of tickets decreased from 53 to 52 comparing to 17.01. But the DoD change measure shows 52.
