topic Re: Year over Year Data Comparison in Developer

Year over Year Data Comparison

Ashish_kumar12 — Fri, 06 Nov 2020 09:00:49 GMT

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

Re: Year over Year Data Comparison

lbendlin — Sat, 07 Nov 2020 02:22:01 GMT

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.

Re: Year over Year Data Comparison

v-lionel-msft — Thu, 12 Nov 2020 06:59:33 GMT

Hi @Ashish_kumar12 ,

Try this.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Year over Year Data Comparison

Ashish_kumar12 — Wed, 18 Nov 2020 15:50:07 GMT

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

Re: Year over Year Data Comparison

v-lionel-msft — Thu, 19 Nov 2020 05:57:31 GMT

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 Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.