March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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.
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
Proud to be a Super User!
Hey @schaema3
You may have better luck using the PARALLELPERIODS function: https://docs.microsoft.com/en-us/dax/parallelperiod-function-dax
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
99 | |
88 | |
70 | |
61 |
User | Count |
---|---|
141 | |
122 | |
105 | |
94 | |
90 |