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

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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'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