Parallelperiod DAX issue

[hidenseek9]

Hello Power BI Community!

I am new to Power BI and currently facing an issue with parallelperiod DAX function.

Hope someone in the community has an answer to this!

I build a power BI to analyze revenue on current year (CY) vs last year (LY).

So I created a measure to calculate CY and LY data such as below.

CY = calculate(sum(revenue),parallelperiod(XXXX,0,month))

LY = calculate(sum(revenue),parallelperiod(XXXX,-12,month))

Then I calculate the variance as below.

Var = CY - LY

However, the variance comes out wrong for the following reason.

For current year data, let's say I am analyzing revenue from Q1'17 to that of Q1'16.

The revenue date that I have for Q1'17 is from Jan 20 - Mar 10

The revenue date that I have for Q1'16 is from Jan 1- Mar 20.

When I do a parallelperiod fuction with above dates,

Power BI is only taking the revenue data from Jan20-Mar10 for Q1'16,

so it is leaving out the data from Jan 1-20 and Mar 10-20.

How can I solve this issue? What would be an ideal DAX to use for this analysis.

I use timeline Power BI visuals to analyze vs LY analysis on month, quarter or even a full year

so I cannot use DAX such as MTD, or QTD.

Appreciate your help in advance!

H

[v-huizhn-msft · Accepted solution]

Hi @hidenseek9,

You can create a quarter calculated column including Q1,Q2,Q3 and Q4.

Then create a measure using the formula.

CY-quarter=CALCULATE(SUM(Table[revenue]),ALLEXCEPT(Table, Table[quarter]))

Finally, you can create the difference between 2016 and 2017 year.

Var=CALCULATE(Table[CY-quarter],FILTER(Table,Table[year]=2017))-CALCULATE(Table[CY-quarter],FILTER(Table,Table[year]=2016))

Please try the solution above and check if it works. Please don't hesitate to ask if you have other problems.

Best Regards,
Angelia

[hidenseek9]

Hi @v-huizhn-msft

Thank you so much for your advice.

Since I do random LY (month, quarter, ramdomly selected months) comparison,

I did not use the formula suggested.

Instead, I was able to do the comparison by creating a master calendar that has all the dates

from Jan 1 - Dec 31 of all the years in the data and link to the original data source.

Appreciate your quick response and kind support nonetheless!

Hide  

[v-huizhn-msft]

Hi @hidenseek9,

You have resolved your issue, right? If it is, please mark the helpful reply as answer, so that more people can find workaround form here. Thanks for understanding.

Best Regards,
Angelia

[hidenseek9]

@v-huizhn-msft

Yes. The reply was helpful.

Many thanks!

H