Forum Discussion

Krishnan_47's avatar
Krishnan_47
Icon for Helper I rankHelper I
4 years ago
Solved

Dynamic Header Date value in matrix table

Hi,   I have the following data as shown in the screenshot: When user selects a particular date, I need to show current month and previous month data as shown below using a matrix table, the...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Krishnan_47 

    You can create a measure to realize your need .

    (1)Create a calculated table to return the date column from original data table, and add a slicer with the column 'Table 2'[Date]

    Table 2 = SELECTCOLUMNS('Table',"Date",'Table'[Date])

    (2)Create a measure to return current month and previous month data when you select date in slicer .

    select month value = CALCULATE(SELECTEDVALUE('Table'[Value]),FILTER('Table','Table'[Date]<=SELECTEDVALUE('Table 2'[Date]) && 'Table'[Date]>=DATEADD('Table 2'[Date],-1,MONTH)))

    (3)Add the measure to values in Matrix visual .

    Final result is as shown :

    I have attached my pbix file , you can refer to it.

     

    Best Regard

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Krishnan_47 

You can create a measure to realize your need .

(1)Create a calculated table to return the date column from original data table, and add a slicer with the column 'Table 2'[Date]

Table 2 = SELECTCOLUMNS('Table',"Date",'Table'[Date])

(2)Create a measure to return current month and previous month data when you select date in slicer .

select month value = CALCULATE(SELECTEDVALUE('Table'[Value]),FILTER('Table','Table'[Date]<=SELECTEDVALUE('Table 2'[Date]) && 'Table'[Date]>=DATEADD('Table 2'[Date],-1,MONTH)))

(3)Add the measure to values in Matrix visual .

Final result is as shown :

I have attached my pbix file , you can refer to it.

 

Best Regard

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Krishnan_47's avatar
    Krishnan_47
    Icon for Helper I rankHelper I
    4 years ago

    Thank you Ailsa-msft. I have one more additional clarifcation. Please let me know if it is possible to achieve the following scenarios:

    1. Can we add a new column 'Diff' for each Asset? The formula for diff will be (Current Month - Prev Month).

    2. Can we show Total in column level.

     

    Please find below screenshot highlighted in yellow for the two points: