YOY Variance in DAX power BI

overview of the task:

1. Date Table :

   • Ensure you have a proper Date Table in your model with a continuous range of dates. This is required for time intelligence functions like SAMEPERIODLASTYEAR.

2. Handling Missing Data :

   • If there is no data for the previous year, the Previous Year Value will return blank. You can handle this using IF or ISBLANK functions.

YOY Variance = 
IF(
    ISBLANK([Previous Year Value]), 
    BLANK(), 
    [Current Year Value] - [Previous Year Value]
)

1. Custom Time Periods :

   • If you need to calculate YOY variance for custom periods (e.g., quarters), adjust the DATESYTD or use DATESBETWEEN.

Year-over-Year (YOY) variance is a common metric used to compare data from the current year to the same period in the previous year. In Power BI, you can calculate YOY variance using DAX (Data Analysis Expressions) . Below is a step-by-step guide to achieve this.

-- Step 1: Current Year Value
Current Year Value = 
CALCULATE(
    SUM('SalesTable'[SalesAmount]), 
    DATESYTD('DateTable'[Date])  -- Optional: Remove if not calculating YTD
)

-- Step 2: Previous Year Value
Previous Year Value = 
CALCULATE(
    SUM('SalesTable'[SalesAmount]), 
    SAMEPERIODLASTYEAR('DateTable'[Date])
)

-- Step 3: YOY Variance
YOY Variance = 
[Current Year Value] - [Previous Year Value]

-- Step 4: YOY % Change
YOY % Change = 
DIVIDE(
    [YOY Variance], 
    [Previous Year Value]
)

1. CALCULATE :

   • Modifies the context of a calculation. It allows you to apply filters or change the evaluation context.

2. SUM :

   • Aggregates numeric values (e.g., sales, revenue).

3. DATESYTD :

   • Returns a set of dates in the year-to-date period up to the last date in the current context.

4. SAMEPERIODLASTYEAR :

   • Shifts the date context back one year, allowing you to compare the same period in the previous year.

5. DIVIDE :

   • Safely divides two numbers, handling division by zero errors gracefully.

To visualize the YOY variance in Power BI:

1. Add a Line Chart , Bar Chart , or Table to your report.
2. Drag the Date field to the Axis.
3. Drag the Current Year Value, Previous Year Value, YOY Variance, and YOY % Change measures to the Values area.
4. Use conditional formatting to highlight positive or negative variances.