Hi @nor303
To solve your problem, we can use DAX to dynamically calculate the transitions between the periods selected by a slicer, and to handle scenarios such as customers that are no longer present ("Gone"). We'll first calculate the ChangeNr column, which represents the difference in ratings between the current and previous periods for each Kundenr. This can be achieved with the following DAX formula:
ChangeNr =
VAR CurrentPeriod = MAX('Table'[Periode])
VAR PreviousPeriod = CALCULATE(MAX('Table'[Periode]), 'Table'[Periode] < CurrentPeriod)
VAR CurrentRating = MAX('Table'[Rating])
VAR PreviousRating = CALCULATE(MAX('Table'[Rating]), 'Table'[Periode] = PreviousPeriod && 'Table'[Kundenr] = EARLIER('Table'[Kundenr]))
RETURN
IF(
ISBLANK(PreviousRating),
BLANK(),
CurrentRating - PreviousRating
)
Next, we calculate the ChangeID column to categorize the change (e.g., "Better," "Worse," "Unchanged"). This column determines the nature of the change based on the value of ChangeNr. The formula for this is as follows:
ChangeID =
VAR Change = [ChangeNr]
RETURN
SWITCH(
TRUE(),
ISBLANK(Change), "New",
Change > 0, "Better",
Change < 0, "Worse",
Change = 0, "Unchanged",
"Default"
)
For identifying "Gone" customers (those present in the previous period but not in the current period), we can use a measure. This measure checks whether a customer exists in the earlier period but not in the later period:
Gone =
VAR CurrentPeriod = MAX('Table'[Periode])
VAR PreviousPeriod = CALCULATE(MAX('Table'[Periode]), 'Table'[Periode] < CurrentPeriod)
RETURN
IF(
NOT(ISBLANK(CALCULATE(MAX('Table'[Kundenr]), 'Table'[Periode] = PreviousPeriod))) &&
ISBLANK(CALCULATE(MAX('Table'[Kundenr]), 'Table'[Periode] = CurrentPeriod)),
1,
0
)
To create a transition matrix that dynamically calculates the "From Rating" and "To Rating" based on the selected periods, we use the following measure. This measure evaluates the rating transitions for each Kundenr:
Transition =
VAR CurrentPeriod = MAX('Table'[Periode])
VAR PreviousPeriod = CALCULATE(MAX('Table'[Periode]), 'Table'[Periode] < CurrentPeriod)
VAR FromRating = CALCULATE(MAX('Table'[Rating]), 'Table'[Periode] = PreviousPeriod && 'Table'[Kundenr] = EARLIER('Table'[Kundenr]))
VAR ToRating = CALCULATE(MAX('Table'[Rating]), 'Table'[Periode] = CurrentPeriod && 'Table'[Kundenr] = EARLIER('Table'[Kundenr]))
RETURN
IF(
ISBLANK(FromRating) || ISBLANK(ToRating),
BLANK(),
FromRating & " -> " & ToRating
)
For the matrix visualization, we use the calculated From Rating and To Rating columns as the rows and columns, respectively. The values in the matrix represent the count of Kundenr for each transition. Additionally, we create a measure to count customers classified as "Gone" for a selected period:
Gone Customers Count =
CALCULATE(
COUNTROWS('Table'),
'Table'[Gone] = 1
)
This approach allows for a dynamic and interactive matrix visualization where the transitions and categories adjust based on the slicer selection. By incorporating measures for detecting "Gone" customers and dynamically calculating transitions, you can create a complete and responsive solution in Power BI. Let me know if further clarification or adjustments are needed!
Best regards,
