I have a single measure in the Power BI Desktop matrix below.
The measure comes from a single table, and is a sum of a column with numerical values.
The measure is: SUM(Table[Column])
Column Group 1: Category
Column Group 2: Year
Column Group 3: Qtr (i.e. Q)
I have a scenario, in which our client wants to get the variance (difference) between 2020 Q1 of Category A and 2020 Q1 of Category B.
The new column is Q1 Variance.
In a similar manner I need the variance between 2020 Q2 of Category A and 2020 Q2 of Category B.
How do I get a new column in a matrix based on this scenario, where I subtract corresponding columns in a Year and Quarter?
Also there may be situation later in which Category A can have a year that Category B may not have.
For example, Category A may have 2018 and 2019, while Category B may have 2017 and 2018.
In this scenario, I will need the difference between the 2018 Quarters only, don't need any computation on 2017 and 2019.
Any ideas please, using DAX?
The Category selection happens via a Slicer.
Sometimes it may be Category A and Category B, other times it may be Category B and Category C, that are chosen from the Slicer.
The slicer will always choose exactly 2 categories at a time.
So, I cannot hard code anything. Everything is dynamic.
Category A may have 2018 and 2019, while Category B may have 2017 and 2018, while Category C may have 2019, 2020, 2021, etc.
Also, I might have to compare the corresponding months too. Say Jan 2019 of A will be subtracted from Jan 2019 of B (similar to the Quarter comparison).
I think we may need to use some variable to match the year, and then move to quarter, and later to month.