Create % comparison value between two visuals

Question

I am trying to create a new dynamic comparison metric between two table visuals with identical metrics and custom date slicers that create a period A/B view.

Both tables and date slicers reference the same dataset (tableA). I want to create a measure that can calculate the % difference for all metrics between periods A and B, either as a new table or a series of scorecards under the period B table.

For simplicity, I am only using Cost and Date from the table to create these different periods for comparison.

I am not a DAX expert, so I am running into issues with creating my measure since it relies on the same data set. The closest I got was by duplicating my dataset (tableA (1)) so that I could reference the same metric in my calculation, i.e. %_Change_Cost=(SUM(('tableA'[Cost])/('tableA (1)'[Cost]))-1. But when the date filters do not overlap, the calculation breaks.

Period A vs B tables

Thank you!

Answer 1

Instead of duplicating the fact table I would duplicate the Date table, having DateA, and DateB, both with relationship to fact table date.

Then use each Date table for each period A and B. and adjust interactions to disable filtering period A visuals with Date B slicer and period B visuals with Date A slicer.

This formula compute the cost difference between A and B, by :

• summing the cost for selected dates in DateA removing filters on DateB
• summing the cost for selected dates in DateB removing filters on DateA
• compute the ratio of difference over period A (adapt to your need)

Make sure to display in a visual with both DateA and DateB slicers are enabled.

% CostIncreaseAB = 
    VAR CostA = CALCULATE(
        SUMX(Table, 'Table'[Cost]),
        ALL(DateB[Date])) - 
    VAR CostB = CALCULATE(
        SUMX(Table, 'Table'[Cost]),
        ALL(DateA[Date])) 
    RETURN 
        DIVIDE(CostB - CostA, CostA)