cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
smiller987 Frequent Visitor
Frequent Visitor

Having an Issue with PARALLELPERIOD and a Formatted Date in X-Axis

Hello,

 

I am having a very annoying issue with PARALLELPERIOD.  I have a chart that shows the change from prior quarter end to current quarter end.  I have two fields, one that is a date field (QE Date) with the date for the quarter end, another (Quarter) that is a new column based on QE Date and is formatted to show the four-digit year + the letter Q, then the quarter number.

 

I have multiple charts on the page looking at the same field in various ways.  Most of them work fine with either QE Date or Quarter in the x-axis.  The others that work only look at the current quarter.

 

When, on the chart showing change over prior quarter, I use QE Date on the x-axis, it works fine.  When I switch to using Quarter, it seems that it only finds the current quarter, and the value for the prior quarter evalutes to 0, which of course ends up erroneously showing that the change is the full amount of the current quarter.  My boss wants me to use the formatted date, but I have not found any way to make it work.

 

I have tried implementing a date dimension table, which yielded the same results as I got without it.

 

My DAX for the measure is:

New Auto Balances Prior Quarter = CALCULATE(SUM(NCUA5300[New Auto Balance per Member]),PARALLELPERIOD(NCUA5300[QE Date],-1,QUARTER))

 

I obviously could resolve the issue by preloading the source table (it's an MS SQL database) with the calculated differences in new appended columns, which would eliminate the need for the measure in Power BI, but my boss also does not want me to do that.

 

Here are screenshots demonstrating the issue.  The first shows the quarter over quarter change in balances on the left, but has the unwanted date displaying in the x-axis due to use of the QE Date field.  The second shows the nicely formatted Quarter in the x-axis on the left, but the quarter over quarter change is incorrect, showing as equal to the actual balance for that quarter.  In both images, the current actual balance for each quarter is on the right.

 

change over q correct.pngchange over q wrong.png

 

 

Thanks in advance for any assistance!

1 ACCEPTED SOLUTION

Accepted Solutions
smiller987 Frequent Visitor
Frequent Visitor

Re: Having an Issue with PARALLELPERIOD and a Formatted Date in X-Axis

Hello,

 

Thank you for responding.  I resolved the issue myself last week.  It turned out that there was a report level filter referencing the original quarter end date field.  After changing it to use the date key in my date dimension table, the other issues immediately disappeared.

 

It seems that the lesson here, for anyone experiencing similar issues who happens to stumble into this thread, if you've implemented a date dimension table, make sure that you look everywhere for any other references to your date(s) and try changing to using the appropriate field in your date dimension table instead.

 

Thanks,

Steve

2 REPLIES 2
v-huizhn-msft Super Contributor
Super Contributor

Re: Having an Issue with PARALLELPERIOD and a Formatted Date in X-Axis

Hi @smiller987,

The DAX formula measure shows in the right screenshot? What's your Change in Auto Balances? It is (New Auto Balances-New Auto Balances Prior Quarter), right?

PARALLELPERIOD is used to continuous date, if you use the Quarter Date, the date is not continuous.

You can create a calculated column to get previous quarter date value in your resouce table using the similar formula.

Previous quarter date= LOOKUPVALUE(NCUA5300[value],NCUA5300[DATE],DATEADD(NCUA5300[DATE],-1,QUARTER))


For my sample table as example, I get the correspinding prior quarter date value. The value 57 is the value in Q1 2013/1/1.

1.PNG

Then you use the new column [Previous quarter date] to calculate [New Auto Balances Prior Quarter] similar the following formula.

New Auto Balances Prior Quarter = sum([New Auto Balances Prior Quarter])


Finally, select Quarter as X-axis, and check if it works.

Best Regards,
Angelia

smiller987 Frequent Visitor
Frequent Visitor

Re: Having an Issue with PARALLELPERIOD and a Formatted Date in X-Axis

Hello,

 

Thank you for responding.  I resolved the issue myself last week.  It turned out that there was a report level filter referencing the original quarter end date field.  After changing it to use the date key in my date dimension table, the other issues immediately disappeared.

 

It seems that the lesson here, for anyone experiencing similar issues who happens to stumble into this thread, if you've implemented a date dimension table, make sure that you look everywhere for any other references to your date(s) and try changing to using the appropriate field in your date dimension table instead.

 

Thanks,

Steve