Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Community,
I'm working on a Power BI report and have run into a problem with my Year-over-Year (YoY) comparisons. Here's the scenario:
I have a table called "Market Revenue" with the following relevant columns:
Additionally, I have a date table that helps me build time-based relationships in Power BI. Both tables are related through the Date columns.
As of now, I have a measure that calculates how each month is doing compared to the month in the previous year, calculating the percentages from the month selected in a slicer back to 13 months ago. The measure is as follows:
The measure is used in the following line chart:
Here's the challenge:
I need to create a YoY measure that compares the correct data based on these rules:
My measure doesn't currently consider the column 'Logic' ("old" or "new") when performing the YoY comparison.
As you can see in the line chart, because of the double values in 2023, the comparison 2023 to 2022 is too high (above 100%) and 2024 to 2023 is too low.
I would appreciate your guidance on any adjustments needed to my existing DAX measure to achieve the correct results.
Thank you in advance for your help!
Hi, @pvdkamp
Based on your description, your DAX expression might look something like this:
P13M Market MTD YoY % Revised =
VAR ReferenceDate = MAX ( DateTable[Date] )
VAR ReferenceYear = YEAR ( ReferenceDate )
VAR LogicToUse = SWITCH (
TRUE (),
ReferenceYear = 2024, "new",
ReferenceYear = 2023, "old",
"old"
)
VAR ReferenceDatePY = DATE ( YEAR ( ReferenceDate ) - 1, MONTH ( ReferenceDate ), DAY ( ReferenceDate ) )
VAR PreviousDates = DATESINPERIOD ( DateTable[Date], ReferenceDate, -13, MONTH )
VAR PreviousDatesPY = DATESINPERIOD ( DateTable[Date], ReferenceDatePY, -13, MONTH )
VAR P13M_Market_MTD_PY = CALCULATE (
SUM ( 'Market Revenue'[Revenue] ),
FILTER (
'Market Revenue',
'Market Revenue'[Logic] = LogicToUse
),
SAMEPERIODLASTYEAR ( DateTable[Date] ),
REMOVEFILTERS ( DateTable ),
KEEPFILTERS ( PreviousDatesPY )
)
VAR P13M_Market_MTD_YoY = DIVIDE ( [P13M Market MTD], P13M_Market_MTD_PY ) - 1
VAR P13M_YoY_CALC = CALCULATE (
P13M_Market_MTD_YoY,
REMOVEFILTERS ( DateTable ),
KEEPFILTERS ( PreviousDates )
)
VAR Result = IF ( P13M_YoY_CALC = -1, BLANK (), P13M_YoY_CALC )
RETURN
Result
Are you able to provide some sample data? Please don't include any sensitive data.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @v-jianpeng-msft! Of course I can provide you with some sample data. Your help is very much appreciated!! https://www.dropbox.com/scl/fi/6fo4qu0bmxakc76assv62/Sample.xlsx?rlkey=3v71ecx4v899m4emr4pex2l2i&st=...
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
88 | |
85 | |
68 | |
68 | |
64 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |