Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
schaema3
Advocate III
Advocate III

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.

2 REPLIES 2
vanessafvg
Super User
Super User

i think you need to actually set what your financial date is example

 

 

QTD Measure =
TOTALQTD ( SUM ( Table[Column] ), 'Calendar'[Date], '03/31' )

the last bit in red shows when your year finishes 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Tad17
Solution Sage
Solution Sage

Hey @schaema3 

 

You may have better luck using the PARALLELPERIODS function: https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

October NL Carousel

Fabric Community Update - October 2024

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