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

amitchandak's avatar
amitchandak
Super 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