Forum Discussion

dgeg's avatar
dgeg
Frequent Visitor
2 years ago
Solved

help with calculation

Hello!

 

Looking for help writing a formula:

 

Year over year comparison by month.

The oldest data I have is Jan 2023.

 

I have the following so far:

YoY Var =
IF(
    ISFILTERED('Calendar'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_MONTH = CALCULATE([Monthly Spend], DATEADD('Calendar'[Date].[Date], -12, MONTH))
    RETURN
        CALCULATE([Monthly Spend] - __PREV_MONTH)
)
 
 

 

I do not want YoY Var to populate for 2023 because there is no historical data to compare to and I do not want 2024 YoY Var to populate in months that have not happened yet.

 

 

Any help woul dbe appreciated. Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi dgeg ,

     

    You can try formula like below:

     

    YoY Var =
IF (
    ISFILTERED ( 'Calendar'[Date] ),
    ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." ),
    VAR __CURR_MONTH = [Monthly Spend]
    VAR __PREV_YEAR_MONTH =
        CALCULATE (
            [Monthly Spend],
            FILTER (
                ALL ( 'Calendar' ),
                YEAR ( 'Calendar'[Date] )
                    = YEAR ( MAX ( 'Calendar'[Date] ) ) - 1
                    && MONTH ( 'Calendar'[Date] ) = MONTH ( MAX ( 'Calendar'[Date] ) )
            )
        )
    RETURN
        IF (
            YEAR ( MAX ( 'Calendar'[Date] ) ) > 2023
                && MONTH ( MAX ( 'Calendar'[Date] ) ) > MONTH ( TODAY () ),
            BLANK (),
            IF (
                NOT ( ISBLANK ( __PREV_YEAR_MONTH ) ),
                __CURR_MONTH - __PREV_YEAR_MONTH,
                BLANK ()
            )
        )
)

     

     

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies