Calculating YOY Change for Measure

jmorela1 To have a better use of the time intelligence functions, i suggest you to create a DateTable and a column with the lastdateofmonth in the Summary Table:

1- Add a Calculated Column in the Summary table:

Last Day of Month = 
EOMONTH(
    DATEVALUE("1 " & [Month] & " " & [Year]),
    0
)

2. create a new table to be use as a DateTable:

DateAutoTemplate = 
--  
--     Configuration
--  
VAR __FirstDayOfWeek = 0
----------------------------------------
VAR __WeekDayCalculationType = IF ( __FirstDayOfWeek = 0; 7; __FirstDayOfWeek ) + 10
VAR __Calendar = 
    VAR __FirstYear = 2022
    VAR __LastYear = YEAR ( TODAY() )
    RETURN CALENDAR (
        DATE ( __FirstYear; 1; 1 );
        DATE ( __LastYear; 12; 31 )
    )
VAR __Step3 = 
    GENERATE (
        __Calendar;
        VAR __IsStandardLocale = IF ( FORMAT( DATE( 2000; 1; 1 ); "oooo" ) = "oooo"; TRUE; FALSE )
        VAR __MonthFormatString = IF( __IsStandardLocale; "mmm"; "ooo" )
        VAR __DayFormatString = IF( __IsStandardLocale; "ddd"; "aaa" )
        VAR __LastTransactionDate = TODAY()
        VAR __Date = [Date]
        VAR __YearNumber = YEAR ( __Date )
        VAR __QuarterNumber = QUARTER ( __Date )
        VAR __YearQuarterNumber = CONVERT ( __YearNumber * 4 + __QuarterNumber - 1; INTEGER )
        VAR __MonthNumber = MONTH ( __Date )
        VAR __WeekDayNumber = WEEKDAY ( __Date; __WeekDayCalculationType )
        VAR __WeekDay = FORMAT ( __Date; __DayFormatString )
        RETURN ROW ( 
            "Year"; __YearNumber;
            "Year Quarter Number"; __YearQuarterNumber;
            "Year Quarter"; FORMAT ( __QuarterNumber; "\Q0" ) & "-" & FORMAT ( __YearNumber; "0000" );
            "Quarter"; FORMAT( __QuarterNumber; "\Q0" );
            "Year Month"; FORMAT ( __Date; __MonthFormatString & " yyyy" );
            "Year Month Number"; __YearNumber * 12 + __MonthNumber - 1;
            "Month"; FORMAT ( __Date; __MonthFormatString );
            "Month Number"; __MonthNumber;
            "Day of Week Number"; __WeekDayNumber;
            "Day of Week"; __WeekDay;
            "DateWithTransactions"; __Date <= __LastTransactionDate 
        )
    )
RETURN
    __Step3

2.1 mark this table as a DateTable:
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-time-intelligence#the-calendar-options-screen

2.2 create the relationship (one-to-many) between the DateAutoTemplate[Date] and the Sumary[Last day of Month]

3. use the intelligence functions as needed:
--> for each measure use the same logic:::

Conv Rate Measure =
DIVIDE (
    [Total Conversions Measure];
    [Total Impressions Measure]
)

PY Conv Rate Measure = 
    CALCULATE (
        [Conv Rate Measure];
        CALCULATETABLE (
            DATEADD ( 'Date'[Date]; -1; YEAR );
            'Date'[DateWithTransactions] = TRUE
        )
    )

YOY Conv Rate Measure = 
VAR __ValueCurrentPeriod = [Conv Rate Measure]
VAR __ValuePreviousPeriod = [PY Conv Rate Measure]
VAR __Result =
    IF (
        NOT ISBLANK ( __ValueCurrentPeriod ) && NOT ISBLANK ( __ValuePreviousPeriod );
        __ValueCurrentPeriod - __ValuePreviousPeriod
    )
RETURN
    __Result

YOY % Conv Rate Measure = 
DIVIDE ( 
    [YOY Conv Rate Measure];
    [PY Conv Rate Measure]
)

 
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster.

Hi jmorela1 

Thank you for reaching out to the Microsoft Fabric community forum.

To ensure the YOY calculation aligns with your business expectation, could you please share the expected output (for example, a screenshot or sample values for one or two months)?This will help confirm whether YOY should be calculated as a simple year-over-year percentage change on the final KPI or using a different business rule, and will allow us to identify the exact root cause more accurately.

 

Best regards,

Microsoft Fabric Community Support Team.
 

Hi jmorela1 

Reviewing your details, the reason ROAS YOY is not displayed for April is because the prior year had a zero baseline, not due to any DAX or Power BI issue. In April 2022, Spend was $6,897 and Revenue was $0, resulting in a ROAS of 0. For April 2023, Spend was $608 and Revenue was $131,968, giving a ROAS of about 217. The YOY % is calculated as (Current ROAS − Prior ROAS) ÷ Prior ROAS, but since the prior ROAS is 0, this leads to a division by zero, making the calculation undefined. Power BI returns BLANK in such cases to prevent misleading results, which is expected for ratio metrics like ROAS, CPM or conversion rate if the previous period had zero revenue or spend.

Leave ROAS YOY blank when the prior year has zero revenue or ROAS and optionally add a note or tooltip explaining this. If you still need a workaround, I suggest using the first non-zero month, a fixed baseline or a business defined minimum denominator. Make sure these are clearly labeled as adjusted metrics since they do not show actual YOY performance.

Regards,

Microsoft Fabric Community Support Team

Hi jmorela1 

Even after expanding the dataset, this behavior is expected because YOY calculations are performed at the specific comparison level (Year, Month, and all active filters), not across the dataset as a whole. To verify, you can create a simple table with Year, Month, and ROAS Measure and apply the same slicers as in the YOY view. If April in the previous year shows 0 or BLANK for ROAS at that level, YOY cannot be calculated, no matter how much additional data is present for other periods. This is standard for ratio-based KPIs like ROAS, CPM, and conversion rate.

The recommended approach is to leave ROAS YOY blank when prior-period ROAS is zero, and optionally add an explanation using a tooltip or note. If a comparison is still needed, you can use a workaround such as the first non-zero month, a fixed baseline, or a minimum denominator, but it should be clearly labeled as an adjusted metric rather than a true YOY value.

Regards,

Microsoft Fabric Community Support Team.

Hi jmorela1 

I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.

Thank You.

Hi jmorela1 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.