Forum Discussion
Creating a dynamic card visual
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.
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!
4 Replies
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!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?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?
