Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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, @Anonymous
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 @Anonymous! 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=rkhj603x&dl=0
User | Count |
---|---|
77 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
100 | |
93 | |
52 | |
50 | |
48 |