Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to Compare Current and Prior Rolling 12 Months using Two Line Charts in Power BI

I need your help to be able to create a line chart that shows two lines, one for the current 12 months rolling period, and another one that shows the prior rolling 12 months. The intended results should always show the current month on the right-hand side of the graph (for example the current 12 months as of today are from April 2022 to March 2023) 

This is what I have tried so far: To show two lines of the current twelve months period and compare the same period with the prior dates in the same line chart graph in Power BI, I followed these steps below:

 

Create a measure to calculate the current rolling twelve months values for your Y-axis measure. In my case, the measure is "Appointments in Current 12M". Using the DATESBETWEEN function I calculated the values for the past 12 months.

 

Appointments in Current 12M = CALCULATE( DISTINCTCOUNT(SalesActivation[LocationCodes]), DATESBETWEEN( SalesActivations[periode_date], DATEADD(MAX(SalesActivation[periode_date]),-11,MONTH), MAX(SalesActivation[periode_date]) ), FILTER(SalesActivation, SalesActivation[OrganiserData]="1") )

 

Then I created a measure to calculate the prior rolling twelve months' values for my Y-axis measure. Using the SAMEPERIODLASTYEAR function in combination with DATESBETWEEN to calculate the values for the same period in the previous year.

 

Appointments in Prior 12M = CALCULATE( DISTINCTCOUNT(SalesActivation[LocationCodes]), SAMEPERIODLASTYEAR(SalesActivation[periode_date]), DATESBETWEEN( SalesActivation[periode_date], DATEADD(MAX(SalesActivation[periode_date]),-23,MONTH), DATEADD(MAX(SalesActivation[periode_date]),-12,MONTH) ), FILTER(SalesActivation, SalesActivation[OrganiserData]="1") )

 

Create a measure for the X-axis values. In my case, the measure is "Period". 

 

Period = FORMAT(SalesData[periode_date], "mmm yyyy")

 

I then created a line chart visual in Power BI. I dragged the "Period" measure to the Axis field well, and the two measures I created earlier to the Values field well. This should display two lines representing the current rolling twelve months values and the prior rolling twelve months values, respectively, and compare them in the same line chart graph in Power BI, but the results and calculations did not work for me. Kindly provide me with your valuable assistance. 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    I managed to resolve the problem by creating the following two measures for the Y Axis

    12 M Prior = CALCULATE([MyDaxMeasureValues], DATESINPERIOD('Calendrier'[Date],ENDOFMONTH(dateadd(SalesActivation[periode_date],-12,month)),-12,MONTH))

    Current 12 M = CALCULATE([MyDaxMeasureValues], DATESINPERIOD('Calendrier'[Date], ENDOFMONTH(SalesActivation[periode_date]), -12, MONTH))

    The I included the following measure in x axis field:

    PeriodCal =
    VAR __maxDate = MAX(Calendrier[Date])
    VAR __minDate = MIN(Calendrier[Date])
    RETURN
    SWITCH(
    TRUE(),
    Calendrier[Date] = __maxDate, "Current Month",
    Calendrier[Date] = __minDate, "Oldest Month",
    FORMAT(Calendrier[Date], "mmm yyyy")
    )







4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous Try the technique here. You can switch this to 12 months by editing the -3 in EOMONTH to -12 and changing the AVERAGEX to SUMX: Better Rolling Average - Microsoft Power BI Community

    Better Rolling Average = 
    VAR __EndDate = MAX('Table'[Date])
    VAR __3MonthsAgo = EOMONTH(__EndDate, -3)
    VAR __StartDate = DATE(YEAR(__3MonthsAgo), MONTH(__3MonthsAgo), 1)
    VAR __Table = 
        SUMMARIZE(
            FILTER(ALL('Table'),[Date]>=__StartDate && [Date]<=__EndDate),
            'Table'[Month],
            "__Value",SUM('Table'[Value])
        )
RETURN
    AVERAGEX(__Table,[__Value])
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for sharing Greg, nonetheless, this calculation did not resolve my problem, I still could not display the current 12 rolling months and the prior rolling 12 months on the same line chart. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        I managed to resolve the problem by creating the following two measures for the Y Axis

        12 M Prior = CALCULATE([MyDaxMeasureValues], DATESINPERIOD('Calendrier'[Date],ENDOFMONTH(dateadd(SalesActivation[periode_date],-12,month)),-12,MONTH))

        Current 12 M = CALCULATE([MyDaxMeasureValues], DATESINPERIOD('Calendrier'[Date], ENDOFMONTH(SalesActivation[periode_date]), -12, MONTH))

        The I included the following measure in x axis field:

        PeriodCal =
        VAR __maxDate = MAX(Calendrier[Date])
        VAR __minDate = MIN(Calendrier[Date])
        RETURN
        SWITCH(
        TRUE(),
        Calendrier[Date] = __maxDate, "Current Month",
        Calendrier[Date] = __minDate, "Oldest Month",
        FORMAT(Calendrier[Date], "mmm yyyy")
        )