Forum Discussion
Year over Year Data Comparison
Hi There ,
Hope you doing well,
I want to create an measure showing Year to Year Total sales in column chart however i want that calculation to pick automatically upcoming year data also e.g 2017,2018,2019,2020 and so on...
I am using this Dax:
Sales LY=calculate(Total[Sales],dateadd(dates(date),-1,Year))
but by this process i have to make each year measure ...So is there any formula where i can use single measure and all the years data will appear in one go
I really appreicate if anyone can help me in this.
Regards,
Ashish
Hi Ashish_kumar12 ,
First I created a calendar table.
Table = CALENDAR( MIN(Sheet2[date]), MAX(Sheet2[date]) )Then I created table relationship.
Then I used your measure formula.
Sales LY = CALCULATE( SUM(Sheet2[Value]), DATEADD( 'Table'[Date], -1, YEAR ) )Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Look at the "Quick Measures" option in Power BI, it has templates for such a scenario.
Make sure that you have a proper calendar table, too.
Hi Ashish_kumar12 ,
First I created a calendar table.
Table = CALENDAR( MIN(Sheet2[date]), MAX(Sheet2[date]) )Then I created table relationship.
Then I used your measure formula.
Sales LY = CALCULATE( SUM(Sheet2[Value]), DATEADD( 'Table'[Date], -1, YEAR ) )Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Ashish_kumar12 ,
Try this.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Lionel,
Thank you for your reply and the example which you showed.
Unfortunatley i am not able to open your pbix file, could you please share me Dax here if possible ?
Regards,
Ashish
