Forum Discussion

Marico's avatar
Marico
Helper IV
1 year ago
Solved

Combining matrix

Hi,   I have the following requirements from the user not sure how many of them are possible.   Model Structure: Source and Target data are coming from two different database tables. There is a ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Marico ,

     

    According to your description, you want to display the data of the current month and the previous five months, and users can use slicer to change the columns in the matrix. Currently, the built-in slicer does not support the default option. I recommend that you use the preselected slicer. The following is my test for your reference. Since I don't know how your year-end target column is obtained, I only display the data up to the current month.

    Step1. Add preselected slicer. 

    Step2. Custom table and Measure.

    _PreselectedSlicer = DATATABLE(
    "IsDirtySlicer", BOOLEAN, 
    {
        {FALSE()},
        {TRUE()}
    }
)
    Measure 2 = 
VAR _select_date = SELECTEDVALUE('Date'[Month-Year])
VAR _previous =UNION(SELECTCOLUMNS(GENERATESERIES(0,5,1),"test",FORMAT(EDATE(TODAY(),-[Value]),"YYYY-MMM")),ROW("test","CurrentYear"))
RETURN
    IF(_select_date IN _previous,TRUE(),FALSE())

    Step3. The Matrix.

     

    Buttons of preselected slicer.

     

     

     

     

     

    Best regards,

    Mengmeng Li

Marico's avatar
Marico
Helper IV
1 year ago

Anonymous Thanks for your response. 

 

Let me check with the client on creating cards for the target values.

 

Could you confirm the following as well, please?

 

"I'm using TODAY() to generate a single list that includes the first five months of the current month, so it's dynamic. When it comes to the next month, the preselected slicer will select February and the first five months by default, and the user doesn't need to select them manually."

 

For the blue highlighted part, when the februrary will come then the slicer and table will have the following months appearing in the table and slicer -> 2024-Sep, 2024-Oct, 2024-Nov, 2024-Dec, 2025-Jan, Current Month - Is this correct understanding? If so, then this is as per the requirement.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Marico ,

 

Yes. It is correct understanding.

 

 

Best regards,

Mengmeng Li

  • Marico's avatar
    Marico
    Helper IV
    1 year ago

     Anonymous Anonymous  I have created the following measure (or separate measure for current month and previous month) to calculate the month-over-month percentage change. How can I calculate this percentage change based on the slicer selection? 

     

    Sales MoM% =

    VAR __PREV_MONTH = CALCULATE(

            SUM(Sales[Sales Amount]),

            PREVIOUSMONTH(Sales[Date]),ALL(Date[Month-Year])

        )

      RETURN

        DIVIDE(

            SUM(sales[sales amount]) - __PREV_MONTH,

            __PREV_MONTH

        )