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

[Jack_Reacher]

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. 

[Greg_Deckler]

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

[Jack_Reacher]

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. 

[Jack_Reacher · Accepted solution]

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

)

[rajesh3090]

this measure is giving error

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

)

can you tell me how to achieve this?