Forum Discussion

hheck's avatar
hheck
Frequent Visitor
3 years ago

Year over year Variance - based on the example below - Very Hard

Can I do scenarios variance? I know we can do only with year date.

1 Reply

  • For scenario-based YoY variance (not just calendar year), the standard DATEADD won't work, you need to define your scenarios as a separate dimension and use CALCULATE with explicit filters.

    Basic pattern:

     
    YoY Variance =
    VAR _curr = CALCULATE([Sales], Scenario[Name] = "Current Year")
    VAR _prev = CALCULATE([Sales], Scenario[Name] = "Previous Year")
    RETURN DIVIDE(_curr - _prev, ABS(_prev))

    If the complexity keeps growing (multiple scenarios, multiple metrics), it's worth looking at Flexa Tables on AppSource, YoY and scenario comparison are built-in buttons, no DAX required. Users switch scenarios themselves in the published report.