Variable to store interval for the parallelperiod function

[Gautier]

Hi All,

I have a complex bridge formulat that dinamically compares the sales of the selected period with either the previous month, quarter or year depending on the selection.

For instance is only 2016 12 is selected that the comparative will be 2016 11. But if the selection is 2016 Q4 than the comparative will be 2016 Q3.

I used the isfiltered function to define if the context is filtered on month, quarter or year and depending on the result I used a switch function to change the comparative:

SWITCH (
                    Timeperiod,
                    "MONTH", CALCULATE (
                        [Margin (actual or forecast)],
                        PREVIOUSMONTH ( Dates[Date] )
                    )
                        ,
                    "QUARTER", CALCULATE (
                        [Margin (actual or forecast)],
                        PREVIOUSQUARTER ( Dates[Date] )
                     ),
                    "YEAR", CALCULATE (
                        [Margin (actual or forecast)],
                        PREVIOUSYEAR ( Dates[Date] )
                    )
)

with Timeperiod being a variable defined as follow:

VAR Timeperiod =
    IF (
        ISFILTERED ( Dates[Month (YYYY MM)] ),
        "MONTH",
        IF ( ISFILTERED ( Dates[QUARTER (YYYY QQ)] ), "QUARTER", "YEAR" )
    )

What I would like to do is to simplify that pattern by using the parallelperiod function with a code similar to this:

VAR Timeperiod =
IF (
    ISFILTERED ( Dates[Month (YYYY MM)] ),
    MONTH,
    IF ( ISFILTERED ( Dates[QUARTER (YYYY QQ)] ), QUARTER, YEAR )
)
RETURN

[comparative margin]=calculate([Margin (actual or forecast)],PARALLELPERIOD(Dates[Date],-1,Timeperiod))

However it seems that DAX cannot store the interval parameter in a variable (Timeperiod being my variable in the code above).

Does anyone have any idea how to solve this ?

[v-yuezhe-msft]

Hi @Gautier,

When using PARALLELPERIOD function, we are not able to store interval parameter in a variable.  The value for interval in PARALLELPERIOD Function (DAX) must be one of the following: year, quarter, month.

In your scenario, you would need to use the original DAX formulas to dynamically compare the sales of the selected period.

Thanks,
Lydia Zhang