Forum Discussion

RCro's avatar
RCro
Frequent Visitor
2 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
RCro
Frequent Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    RCro
    Frequent Visitor
    2 years ago

    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