Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

YoY Comparison with Variable Logic in Power BI - Need Help with DAX Measure

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:

  • Year
  • Month
  • Date (derived from DATE([Year], [Month],1))
  • Logic (either "old" or "new")
  • Revenue

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:

 

P13M Market MTD YoY % =
VAR ReferenceDate =
    MAX ( DateTable[Datum] )
VAR ReferenceDatePY =
    MAX ( DateTable[Datum] ) - 365
VAR PreviousDates =
    DATESINPERIOD ( 'PresentationCalendar'[Date], ReferenceDate, -13, MONTH )
VAR PreviousDatesPY =
    DATESINPERIOD ( 'PresentationCalendar'[Date], ReferenceDatePY, -13, MONTH )
VAR P13M_Market_MTD_PY =
    CALCULATE (
        SUM ( 'Market Report'[Revenue] ),
        SAMEPERIODLASTYEAR ( 'PresentationCalendar'[Datum] ),
        REMOVEFILTERS ( DateTable ),
        KEEPFILTERS ( PreviousDatesPY ),
        USERELATIONSHIP ( DateTable[Date], 'PresentationCalendar'[Date] )
    )
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 ),
        USERELATIONSHIP ( DateTable[Date], 'PresentationCalendar'[Date] )
    )
VAR Result =
    IF ( P13M_YoY_CALC = -1, BLANK (), P13M_YoY_CALC )
RETURN
    Result

The measure is used in the following line chart:

 

Here's the challenge:

  • In 2023, the data has two sets of revenue: one calculated with "old" in the field 'Logic' and the other with "new".
  • In 2024, only the new logic ("new") is used.
  • From 2022 and before, only the old logic ("old") is used.

I need to create a YoY measure that compares the correct data based on these rules:

  • When comparing a month in 2024 to the same month in 2023, the 2023 data should use the new logic ("new").
  • When comparing a month in 2023 to the same month in 2022, both should use the old logic ("old).

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!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.