Get last 12 months average

[Anonymous]

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 Startdate where user can select a date.

When user selects a data from StartDate filter, I would like the below calculation to last 12 months worth of data and give the average bill rate.  

Could you please help me how i can amend this measure to calculate average for last 12 months?

CALCULATE(AVERAGE(Query1[Bill Rate]),ALL(Query1[State]),ALLSELECTED(Query1[Division],Query1[Assignment Type],Query1[Speciality],Query1[BILL_RATE_TIER],Query1[STARTDATE]))

[amitchandak]

@Anonymous , Join start date with date from date table and try a formula like

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

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

[Anonymous]

@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 · Accepted solution]

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