Creating different data series based on year

[Anonymous]

Hi experts,

Just wondering what is the best way to achieve the following:

In my table I have Date and volume like below, around 4 years of data:

Date	Volume
26/08/2021	63
19/08/2021	101
12/08/2021	112
5/08/2021	104
29/07/2021	95
22/07/2021	93
15/07/2021	63
8/07/2021	48
1/07/2021	50
24/06/2021	66
17/06/2021	36

I'd like to create on my chart one data series per year, for example:

Date	CurrentYear	CurrentYear -1	CurrentYear-2
26/08/2021	63	99	108
19/08/2021	101	116	50
12/08/2021	112	106	71
5/08/2021	104	98	45
29/07/2021	95	52	43

In order to have the following chart:

What is the best place to do it in Powerquery or PowerPivot?

[amitchandak · Accepted solution]

@Anonymous , Create measures using time intelligence

examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA