Year over Year Data Comparison

[Ashish_kumar12]

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 

[v-lionel-msft · Accepted solution]

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.

[v-lionel-msft]

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.

[Ashish_kumar12]

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

[lbendlin]

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.