Guys, I have the following scenario today with this DAX measure:
Teste Realizado Condicional =
VAR EscopoTrimestre = ISINSCOPE(dCalendario[Trimestre_Ano])
VAR MaxTrimestre = MAX(dCalendario[Trimestre_Ano])
VAR MinTrimestre = MIN(dCalendario[Trimestre_Ano])
VAR RealizadoMax = CALCULATE([Saldo REALIZADO], dCalendario[Trimestre_Ano] = MaxTrimestre)
VAR RealizadoMin = CALCULATE([Saldo REALIZADO], dCalendario[Trimestre_Ano] = MinTrimestre)
VAR TrimSelecionados = CALCULATE(DISTINCTCOUNT(dCalendario[Trimestre_Ano]), ALLSELECTED(dCalendario[Trimestre_Ano], dCalendario[Índice_Trimestre_Ano]))
VAR Variacao = RealizadoMax - RealizadoMin
RETURN
SWITCH(
TRUE(),
EscopoTrimestre, [Saldo REALIZADO],
NOT EscopoTrimestre && TrimSelecionados = 2, Variacao,
BLANK()
)
This measure is in a matrix chart, which TODAY only has the quarter column. Its business rule is that when the user selects ONLY 2 quarters in the quarter slicer, the variation is displayed in the subtotal; otherwise, it remains blank.
Now, I have the following challenge: the business area has asked me for the variation at the year and month level, that is, the hierarchy would be Year > Quarter > Month.
I have tried several ways to find a DAX measure that solves my problem, but so far without success. The rule is as follows:
When I am in the YEAR SCOPE and select ONLY 2 years in the year slicer, the variation is displayed in the subtotal; the total (columns) is the actual total. When I go down to the quarter level, the variation should only be displayed in the subtotal when ONLY 2 quarters are selected, even if I select the year slicer, regardless of the quantity, the variation should NOT be calculated; it should appear blank. At the quarter level, the variation will only be calculated when 2 quarters are selected. When the user goes down to the month level, it gets a little more complex:
When the user is at the month level, the variation appears in these cases:
To compare two months of the same year, compare one month from two different years, or compare more than one month from different years (in which case you have to sum the total of each month of the year and compare it to the sum of the total months of the previous year) and bring up the variation.
I tried to do this, but I couldn't follow it because the variation always appeared in the context of the quarter when there were two years selected, for example. I couldn't identify if it's because it considers the quarter as part of the year and therefore the year context influenced it (by counting the quarter within the year), or if it's because the subtotal doesn't know which context it's in. That's why, even if I went to the quarter hierarchy, it ended up accepting the rule of two selected years from the year hierarchy... anyway, I would really appreciate your help.
