Measure is taking long time

Hi! Please advise me on how to optimize the measures: When I am calculating for rollingmonths If Reference date is EOMONTH(TODAY(),-1) then Calculation is very quick instead if I am replacing with ...

Need Help

Bibiano_Geraldo

Super User

1 year ago

Hi anusha_2023 ,
The issue lies in the use of CALCULATE inside EOMONTH, which can significantly slow down performance. This happens because it evaluates the entire table repeatedly.

you can try to use variables like bellow measure:

Hour Rolling Months Matrix Measure 2 =
VAR MaxVoucherDate =
    CALCULATE(
        MAX(RevenuePerHourView[VoucherDate]),
        RevenuePerHourView[TotalQuantity] > 0
    )
VAR TargetDate = EOMONTH(MaxVoucherDate, -1)
RETURN
    CALCULATE(
        SUM(RevenuePerHourView[TotalQuantity]),
        DATESINPERIOD(
            'Date'[Date],
            TargetDate,
            -SELECTEDVALUE(RollingMonthsParameter[RollingMonthsParameter]),
            MONTH
        )
    )

anusha_2023

Helper IV

1 year ago

Than you very much for the quick response. I have tried, but still getting the error.

Bibiano_Geraldo
Super User
1 year ago
Can you share a sample file with no sensitive information, please? anusha_2023

Forum Discussion

Measure is taking long time