PY Quarter Comparison for Incomplete Quarters

[laura_1]

Hi, 

I would like to compare % difference in value between Quarter and Quarter PY, e.g. 2018 Q1 and 2017 Q1. The issue arises where quarters are incomplete - assuming for 2018 Q1 we only have data for January and February, the below formula compares the sum of Jan and Feb 2018 to Jan, Feb and Mar 2017 when I'd like it to compare Jan + Feb 2018 to Jan + Feb 2017 when quarter data is incomplete. How should I adjust the formula to take that into consideration? 

100-Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgoPY)}>}[kpi_value])/
Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgo)}> }[kpi_value])*100)

Thanks very much in advance!

[zzyjordan · Accepted solution]

Hi, Laura

please see my solution by adding a new field in your data load script

DayNumberOfQuarter(%date_key) as DayNumberOfQuarter

And then add the condition to your set analysis where the day number of the quarter PY is less than the day number of quarter as of today

100-Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgoPY)},DayNumberOfQuarter={'<=$(=DayNumberOfQuarter(Today()))'}>}[kpi_value])/
Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgo)}> }[kpi_value])*100)

Hope this helps

ZZ

[laura_1]

Thanks very much, that worked!