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

Quarter over Quarter calculation with Financial Year Slicer

Hi,

 

My company uses a financial year that is different from the calendar year. To calculate quarter over quarter changes I've created an own calendar and calculate the QoQ change with this measure:

QoQ-Q1 FY19 =
VAR __BASELINE_VALUE = SUM('Financial'[Q4 FY18 Revenue])
VAR __VALUE_TO_COMPARE = SUM('Financial'[Q1 FY19 Revenue])
RETURN
IF(
NOT ISBLANK(__VALUE_TO_COMPARE),
(__VALUE_TO_COMPARE - __BASELINE_VALUE)
)

That works perfectly. However, in my dashboard I put a slicer that allows to jump across different financial years by selecting i.e. "FY 2019". If the slicer is set on FY 2019 the calculation for the first quarter must compare data from Q4 FY 2018 with data of Q1 FY 2019. However it does take now the revenue of Q1 FY 2019 and compares it against "0" instead. It's kind of logic as with selecting the slicer to only focus on 2019 it ignores values that fall into FY 2018. What is very weird to me is that the column I created in the table that extracts the Q4 FY 2018 values is getting ignored when slicing into it on the dashboard. The formula I used to create that separate column in the table is the following:

 

Q4 FY18  Revenue = IF('Financial'[FY Year]="2018"&&'Financial'[FY Quarter]="Q4",'Financial'[Revenue],BLANK())

 

The slicer does not slice directly into that data but on an overall slicer that handles my financial year schedules (also for other queries) which is than connected into that query. Is there a work around that logic? Any ideas? That issue only appears on Q1, for all the other quarters that belongs to the same FY it's just working fine.