Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get last 12 months average

Hi all, I have the below measure which calculates the averge bill rate excluding State but including Division, Assignment Type, Speciality, Bill_Rate_Tier & Startdate   I have a filter for the Sta...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    First, please create a Date dimension table and use Date field of Date table in slicer. Then create a measure as below:

    Rolling 12 months average =
VAR _seldate =
    SELECTEDVALUE ( Date[Date] )
VAR _startdate =
    DATE ( YEAR ( _seldate ) - 1, MONTH ( _seldate ) - 1, 1 )
VAR _enddate =
    EOMONTH (
        DATE ( YEAR ( _seldate ), MONTH ( _seldate ) - 1, DAY ( _seldate ) ),
        0
    )
RETURN
    CALCULATE (
        AVERAGE ( Query1[Bill Rate] ),
        DATESBETWEEN ( Query1[StartDate], _startdate, _enddate ),
        ALL ( Query1 )
    )

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak 

 

thanks Amit

 

The below is taking the dates from selected date till the last 12 months.

For example : if 8/28/2020 is selected in the filter, its calculating 12 months from this date. 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))

 

What i would like to do is, take the last day of the previous month and calculate 12 months from this date. 

For example: if 8/28/2020 is selected in the filter, it should calculate from 7/1/2019  till 7/31/2019

 
How can modify the DAX to suit my requirement.
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

First, please create a Date dimension table and use Date field of Date table in slicer. Then create a measure as below:

Rolling 12 months average =
VAR _seldate =
    SELECTEDVALUE ( Date[Date] )
VAR _startdate =
    DATE ( YEAR ( _seldate ) - 1, MONTH ( _seldate ) - 1, 1 )
VAR _enddate =
    EOMONTH (
        DATE ( YEAR ( _seldate ), MONTH ( _seldate ) - 1, DAY ( _seldate ) ),
        0
    )
RETURN
    CALCULATE (
        AVERAGE ( Query1[Bill Rate] ),
        DATESBETWEEN ( Query1[StartDate], _startdate, _enddate ),
        ALL ( Query1 )
    )

Best Regards

Rena