Forum Discussion
DAX comparing two periods using slicers
Hi All,
I am struggeling to do a simple period over period comparison based on the below example. I want to use two slicers: 1) called current priod where I can select a date, and once slicer for previous period where I want to select another date to compare the amount.
I have listed the data and the DM for reference.
Many thanks.
Hi Anonymous ,
I suggest you to create measures as below.
Previous Period = SELECTEDVALUE(previous_dt[Date])Current Period = SELECTEDVALUE(Trial3[Date])Current Amount = IF(ISFILTERED(Trial3[Date]),CALCULATE(SUM(Trial3[Amount])))Previous Amount = CALCULATE(SUM(Trial3[Amount]),FILTER(ALL(Trial3),Trial3[Date] = [Previous Period]))Diff = [Current Amount] - [Previous Amount]Result is as below.
You can also create a measure to filter previous period slicer to only show dates before current period.
Only Show Previous = IF(MAX(previous_dt[Date]) < [Current Period],1,0)Add this measure into visual level filter of the slicer and set it to show items when value =1.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- OwenAuger
Super User
Hi Anonymous
I would recommend the method in this article on DAX Patterns:
https://www.daxpatterns.com/comparing-different-time-periods/
Under this method, you set up two date tables: 'Date' and 'Comparison Date' with relationships as described in the article.
Then you can write measures similar to [Comparison Sales Amount].
regards
- lbendlin
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Hi Anonymous ,
I suggest you to create measures as below.
Previous Period = SELECTEDVALUE(previous_dt[Date])Current Period = SELECTEDVALUE(Trial3[Date])Current Amount = IF(ISFILTERED(Trial3[Date]),CALCULATE(SUM(Trial3[Amount])))Previous Amount = CALCULATE(SUM(Trial3[Amount]),FILTER(ALL(Trial3),Trial3[Date] = [Previous Period]))Diff = [Current Amount] - [Previous Amount]Result is as below.
You can also create a measure to filter previous period slicer to only show dates before current period.
Only Show Previous = IF(MAX(previous_dt[Date]) < [Current Period],1,0)Add this measure into visual level filter of the slicer and set it to show items when value =1.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
