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 ...
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:] )
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