Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating different data series based on year

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:

 

DateVolume
26/08/202163
19/08/2021101
12/08/2021112
5/08/2021104
29/07/202195
22/07/202193
15/07/202163
8/07/202148
1/07/202150
24/06/202166
17/06/202136

 

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

DateCurrentYearCurrentYear -1CurrentYear-2
26/08/20216399108
19/08/202110111650
12/08/202111210671
5/08/20211049845
29/07/2021955243

 

In order to have the following chart:

 

 

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

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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