Any possibility to optimize below DAX query to use in SSAS Tabular Cube?

[KoppulaPSR]

Dear Experts,

I would like to check, whether we can tune below 2 queries w.r.t perfomance improvment. Presently both queries are executing in ~80 seconds which is impacting my over all report rferesh cycle. Kinldy advise.

Below query i use as a part of Month Over Month Calulcation.

[12MonthTotalMoM] :=
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-1,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-2,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-3,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-4,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-5,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-6,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-7,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-8,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-9,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-10,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-11,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-12,MONTH))

Below query i use as a part of Year Over Year Calulcation. Where i take previous year rolling 12 months.

[12MonthTotalYoY] :=
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-12,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-13,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-14,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-15,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-16,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-17,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-18,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-19,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-20,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-21,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-22,MONTH))+
CALCULATE([NrrCustomerRevenueCurrentPeriod],PARALLELPERIOD('Time'[EisPeriodName],-23,MONTH))

[KoppulaPSR]

@AntrikshSharma Your idea is brilliant, now i have used exited ID column in time dimension, which i created with Key Identity. Post impelmentation report refresh cycle reduced by 30%. Needs to tune further of another metrics as well.

[Anonymous]

HI,@KoppulaPSR 
Can you tell me if your problem is solved? If yes, please accept it as solution.

Best Regards,

Leroy Lu

[AntrikshSharma · Accepted solution]

@KoppulaPSR In your date table create a column that will act as a contiguous Year Month Number since 1899-12-30,

The code for the column will be:

Dates[Year] * 12 + Dates[Month Number]

Then you just to filter last 12 Year Month Numbers using:

VAR ActiveYearMonthNumber = 
    MAX ( Dates[Year Month Number] )
VAR Result =
    CALCULATE ( 
        [Total Sales],
        Dates[Year Month Number] >= ActiveYearMonthNumber - 12
            && Dates[Year Month Number] < ActiveYearMonthNumber,
        REMOVEFILTERS ( Dates )
    )
RETURN 
    Result

This calculation will be just like writing

CALCULATE ( 
    [Total Sales],
    PARALLELPERIOD ( Dates[Date], -1, MONTH )
)

...

+ 
CALCULATE ( 
    [Total Sales],
    PARALLELPERIOD ( Dates[Date], -12, MONTH )
)

If you don't want grand total then you can add ISINSCOPE

IF ( 
    ISINSCOPE ( Dates[Year] ), -- Whatever is the top level column in the visual
    VAR ActiveYearMonthNumber = 
        MAX ( Dates[Year Month Number] )
    VAR Result =
        CALCULATE ( 
            [Total Sales],
            Dates[Year Month Number] >= ActiveYearMonthNumber - 12
                && Dates[Year Month Number] < ActiveYearMonthNumber,
            REMOVEFILTERS ( Dates )
        )
    RETURN 
        Result
)

If you want additive sub totals and grand total then you can use SUMX.

SUMX ( 
    VALUES ( Dates[Year Month Number] ),
    VAR ActiveYearMonthNumber = 
        Dates[Year Month Number]
    VAR Result =
        CALCULATE ( 
            [Total Sales],
            Dates[Year Month Number] >= ActiveYearMonthNumber - 12
                && Dates[Year Month Number] < ActiveYearMonthNumber,
            REMOVEFILTERS ( Dates )
        )
    RETURN 
        Result
)