New Measure with Rolling 12 Month

[Elisa_Costanza]

Good afternoon everyone,

I need your support to calculate a DAX measure in a Power BI report.
I have to create a line chart in Power BI, where:

1. On the X-axis I need to place the time interval;

2. On the Y-axis I need to place my measure F100.

   F100 is defined by the following DAX measure:

   
   

   F100 (%) = VAR DefectCount = DISTINCTCOUNT(assunta_datamodel_fact[key_fail]) VAR VINCount = SUM(assunta_datamodel_vehicles_calendar[activity_rate]) RETURN DIVIDE(DefectCount, VINCount, BLANK())

   My calendar is structured as shown in the attached image. 

   

   I need to create a measure that, when a specific Month/Year is selected in the filter, the trend dynamically adapts by considering the MAX date from the rolling 12-month filter.
   So, for example, if I select Month/Year = April 2024 in the filter, the chart should display a trend from April 2023 to April 2024.

   I tried the following DAX formula, but the filter doesn't seem to work correctly:

   
   

   F100 Rolling 12M (%) = VAR SelectedYM = MAX(cv_datamodel_calendar[YearMonthDate]) -- <-- new column of type Date VAR MinYM = EDATE(SelectedYM, -11) VAR DefectCount = CALCULATE( DISTINCTCOUNT(assunta_datamodel_fact[key_fail]), cv_datamodel_calendar[YearMonthDate] >= MinYM, cv_datamodel_calendar[YearMonthDate] <= SelectedYM ) VAR VINCount = CALCULATE( SUM(assunta_datamodel_vehicles_calendar[activity_rate]), cv_datamodel_calendar[YearMonthDate] >= MinYM, cv_datamodel_calendar[YearMonthDate] <= SelectedYM ) RETURN DIVIDE(DefectCount, VINCount, BLANK())

   What am I doing wrong? Can you help me? 

[v-pagayam-msft]

Hi @Elisa_Costanza ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.

[v-pagayam-msft · Accepted solution]

Hi @Elisa_Costanza ,
Thank you @rohit1991  and @rajendraongole1  for the helpful response!

Upon reviewing the provided information,I tried to recreate it locally with the sample data.

1.Create Disconnected Slicer Table using below:

Disconnected_Calendar =

SELECTCOLUMNS(

    Calendar,

    "YearMonthDate", [YearMonthDate]

)

2.Create the F100 Rolling 12M Measure using below:

F100 Rolling 12M (%) =

VAR CurrentYM = MAX(Calendar[YearMonthDate]) -- <- this now uses the X-axis value

VAR MinYM = EDATE(CurrentYM, -11)

VAR SelectedMaxYM = MAX(Disconnected_Calendar[YearMonthDate]) -- from slicer

RETURN

IF (

    CurrentYM <= SelectedMaxYM && CurrentYM >= MinYM,

    VAR DefectCount =

        CALCULATE(

            DISTINCTCOUNT(Fact[key_fail]),

            FILTER(

                ALL(Calendar),

                Calendar[YearMonthDate] >= EDATE(CurrentYM, -11) &&

                Calendar[YearMonthDate] <= CurrentYM

            )

        )

    VAR VINCount =

        CALCULATE(

            SUM(Fact[activity_rate]),

            FILTER(

                ALL(Calendar),

                Calendar[YearMonthDate] >= EDATE(CurrentYM, -11) &&

                Calendar[YearMonthDate] <= CurrentYM

            )

        )

    RETURN DIVIDE(DefectCount, VINCount, BLANK()),

    BLANK()

)

Please refer the attached sceenshot and the file for your reference.

If this solution meets your requiremnet,consider accept it as solution.

Thank you .

Regards,
Pallavi.

[rohit1991]

Hi @Elisa_Costanza ,
To fix your rolling 12-month measure, you need to make sure that the date filter is applied correctly using the FILTER function inside CALCULATE, and that any existing filter context on the date field is removed using ALL. This ensures that the measure always calculates the values over the correct rolling period, regardless of any slicer selections. Below is a revised DAX formula that should provide the correct dynamic behavior when filtering by Month/Year.

F100 Rolling 12M (%) =
VAR SelectedDate = MAX(cv_datamodel_calendar[DAY_DT])
VAR MinDate = EDATE(SelectedDate, -11)
VAR DefectCount =
    CALCULATE(
        DISTINCTCOUNT(assunta_datamodel_fact[key_fail]),
        FILTER(
            ALL(cv_datamodel_calendar),
            cv_datamodel_calendar[DAY_DT] >= MinDate &&
            cv_datamodel_calendar[DAY_DT] <= SelectedDate
        )
    )
VAR VINCount =
    CALCULATE(
        SUM(assunta_datamodel_vehicles_calendar[activity_rate]),
        FILTER(
            ALL(cv_datamodel_calendar),
            cv_datamodel_calendar[DAY_DT] >= MinDate &&
            cv_datamodel_calendar[DAY_DT] <= SelectedDate
        )
    )
RETURN
    DIVIDE(DefectCount, VINCount, BLANK())

Make sure that your cv_datamodel_calendar[DAY_DT] column is marked as a date type and that the table is set as a proper date table in Power BI. This revised measure should now correctly return the rolling 12-month trend for F100 based on the latest date selected in your report.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[Elisa_Costanza]

Thank you @rohit1991 for your feedback. But, using your measure I'm always at the same situation:

Choosing 2025-02 from the slicer, the trend line shows only one value. 

[rajendraongole1]

Hi @Elisa_Costanza  - i guess the filter context isn't correctly applied. can you try below measure:

F100 Rolling 12M (%) =
VAR SelectedYM = MAX(cv_datamodel_calendar[YearMonthDate])
VAR MinYM = EDATE(SelectedYM, -11)

VAR DefectCount =
CALCULATE(
DISTINCTCOUNT(assunta_datamodel_fact[key_fail]),
FILTER(
ALL(cv_datamodel_calendar),
cv_datamodel_calendar[YearMonthDate] >= MinYM &&
cv_datamodel_calendar[YearMonthDate] <= SelectedYM
)
)

VAR VINCount =
CALCULATE(
SUM(assunta_datamodel_vehicles_calendar[activity_rate]),
FILTER(
ALL(cv_datamodel_calendar),
cv_datamodel_calendar[YearMonthDate] >= MinYM &&
cv_datamodel_calendar[YearMonthDate] <= SelectedYM
)
)

RETURN
DIVIDE(DefectCount, VINCount, BLANK())

I hope this helps 

[Elisa_Costanza]

Thank you @rajendraongole1 for your suggestion. But, when selecting a Month/Year from the slicer, the chart only reflects the single selected date rather than showing the full trend from Feb-24 to Feb-25: 

[rajendraongole1]

Hi @Elisa_Costanza  - Try modifying your measure to ignore the slicer selection on YearMonthDate while still considering the selected max Month/Year dynamically.

F100 Rolling 12M (%) =
VAR SelectedYM = MAX(cv_datamodel_calendar[YearMonthDate])
VAR MinYM = EDATE(SelectedYM, -11)

VAR DefectCount =
CALCULATE(
DISTINCTCOUNT(assunta_datamodel_fact[key_fail]),
FILTER(
ALL(cv_datamodel_calendar), -- Ignores slicer filter
cv_datamodel_calendar[YearMonthDate] >= MinYM &&
cv_datamodel_calendar[YearMonthDate] <= SelectedYM
)
)

VAR VINCount =
CALCULATE(
SUM(assunta_datamodel_vehicles_calendar[activity_rate]),
FILTER(
ALL(cv_datamodel_calendar), -- Ignores slicer filter
cv_datamodel_calendar[YearMonthDate] >= MinYM &&
cv_datamodel_calendar[YearMonthDate] <= SelectedYM
)
)

RETURN
DIVIDE(DefectCount, VINCount, BLANK())

Please check and let know

[Elisa_Costanza]

Thank you @rajendraongole1  for your interest. However, this doesn't work correctly in my chart — I’m in confusing. Additionally, I calculated a measure called 'SelectedMaxDate' to check the max date selected in the slicer, and that seems to work

[Elisa_Costanza]

In addition, this is my model data with two table:

1. Fact_Table : assunta_datamodel_fact

2. Dim_Calendar : cv_datamodel_calendar