Creating a dynamic card visual

[RCro]

Hello all!

I have a matrix with Turnover broken down by Date (Year/Month in the rows) and Customer Sales Segment (1-4 based on their purchases, in the columns).

I am trying to create a dynamic card that would display the difference in Turnover between the selected month in the matrix and whichever month comes before it. For example, if I selected the Turnover for Segment 1 in October, the card would display the difference between Segment 1 in October and Segment 1 in September. Alternatively, if I selected the Turnover for Segment 3 in September, it would show me the difference between Segment 3 in September and in August. You get the idea.

I've been trying to use the MTD function to create this card, based on previous posts' suggestions, but this kept displaying Turnover relative to the final month in my data set and did not take into account the selected value.

Any ideas how to create this card? Any help is greatly appreciated! Thanks for taking the time to read this question.

[Anonymous · Accepted solution]

Hello @RCro 

Based on your description, you want to display the difference in Turnover between a selected month for a specific customer sales segment and the previous month for the same segment. To achieve this, you can use DAX measures to calculate the difference dynamically based on the selection in the matrix.

Here's a step-by-step guide to create the dynamic card visual:

1. Create a Measure for Previous Month Turnover

PreviousMonthTurnover = CALCULATE(SUM(YourDataTable[Turnover]),DATEADD('Date'[Date],-1,MONTH))

I suggest adding a Date table as DATEADD functions expects a Date column which has continuous dates. 

2. Create a Measure for Selected Month Turnover

SelectedMonthTurnover = SUM(YourDataTable[Turnover])

3. Create a Measure for Turnover Difference

TurnoverDifference = [SelectedMonthTurnover] - [PreviousMonthTurnover]

4. Add a Card Visual to the Report and drag the TurnoverDifference measure into the Field well of the Card visual.

I have attached a sample file at bottom. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[RCro]

Thank you Jing!

I have tried this solution, but the card only works when a column is selected (i.e, one of the segment names). Is it possible to get the monthly comparison by selecting a single cell or one of the months?

At the moment, there is data for only two months in the data set. Could this be causing the issue?

[Anonymous]

Hi @RCro 

Sorry I'm not very clear about the behavior you met. In my screenshot I selected a single cell and it works well. If possible, can you provide a screenshot after removing sensitive data?

[RCro]

Hi Jing,

I made an example matrix similar to what I'm working with. Screenshot is above.

Jahr represents a Date hierarchy with Year and Month contained within. This comes from a single Date column formatted as YYYY-MM (e.g., 2023-09) that is contained within the same table as Segment and Turnover. I have filtered out Day and Quarter as this data is not available. The 1-4 along the column headers represents the segments whereas the values within represent the Turnover. These three columns are the only data in this table and have been imported from Excel.

As you can see, with the value "20" selected for October, the Previous Month's Turnover formula does not recognise the "10" for September and, subsequently, does not compare the two in the final card ("Leer" is empty in German). This occurs for each value in October. If I select October, it does not recognise the previous month's turnover either. However, if I click on one of the 1-4 column headers that represent Segment, the formulae and associated cards work as intended.

I have copied the formulae you provided and inputted the relevant values, yet I cannot figure out why it isn't working as intended. Any help is greatly appreciated.

Thanks for your time.

RCro