Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Current year volume vs previous 5 year average volume

Hi Everyone

 

What I have:

I have a sales data table with a row for each sale.

I have a calendar lookup table with a row per date.

I have a 'countrows' measure that calculates how many rows are in the table, giving me the total number of sales.

I have a line graph showing the total number of sales over a period of time.

 

Goal:

I would like to be able to display total number of sales, by month, for the current year on a line graph, with the average number of sales, by month, for the previous years on the same graph. Any suggestions on how to get the past 5 years average would be appreciated.

 

Below is an example of what I am looking to achieve.

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Orders count: =
    CALCULATE ( COUNTROWS ( Sales ), Dates[Year] = MAX ( Dates[Year] ) )

     

    Orders count prev 5 years avg: =
    VAR selectedyear =
    MAX ( Dates[Year] )
    VAR previous5years = selectedyear - 5
    VAR newdatetable =
    CALCULATETABLE (
    VALUES ( Dates[Year] ),
    FILTER (
    ALL ( Dates ),
    Dates[Year] < selectedyear
    && Dates[Year] >= previous5years
    )
    )
    RETURN
    AVERAGEX ( newdatetable, [Orders count:] )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Orders count: =
    CALCULATE ( COUNTROWS ( Sales ), Dates[Year] = MAX ( Dates[Year] ) )

     

    Orders count prev 5 years avg: =
    VAR selectedyear =
    MAX ( Dates[Year] )
    VAR previous5years = selectedyear - 5
    VAR newdatetable =
    CALCULATETABLE (
    VALUES ( Dates[Year] ),
    FILTER (
    ALL ( Dates ),
    Dates[Year] < selectedyear
    && Dates[Year] >= previous5years
    )
    )
    RETURN
    AVERAGEX ( newdatetable, [Orders count:] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Jihwan_Kim You're a genius! It worked so, thank you very much for your help.

       

      As a bonus, please could you explain the DAX to me, for the 5yr average? I like to have a vague understanding of what is going on so I know for future.

       

      Many thanks

      James