cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Variable to store interval for the parallelperiod function

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 ?

Microsoft Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors