Matrix to show previous day and MTD performance of the metrics

[Anonymous]

Hi All, is there any way we can achieve the below view in Power BI matrix visual or for that matter any other custom visual?

Example

When i add date and month in rows or in columns it shows drill down options, which is not what i am looking for.

Can someone please suggest how can i do this?

Thank you

[v-eqin-msft]

Hi @Anonymous ,

You could firstly create a table for Matrix visual’s row ,and then use the following formula.

Or take a look at  my pbix file  here.

Flag =
VAR _previous =
    CALCULATE ( MAX ( 'Table'[Date] ) - 1, ALL ( 'Table' ) )
RETURN
    IF (
        DATEDIFF ( 'Table'[Date], _previous, DAY ) = 0,
        "6-Sep",
        IF ( MONTH ( 'Table'[Date] ) = 9, "MTD Sep", BLANK () )
    )

Measure =
SWITCH (
    MAX ( ForMatrix[Martix rows] ),
    "6-Sep", SUMX ( FILTER ( 'Table', 'Table'[Flag] = "6-Sep" ), [Num] ),
    "MTD Sep",
        SUMX (
            FILTER ( 'Table', 'Table'[Flag] = "6-Sep" || 'Table'[Flag] = "MTD Sep" ),
            [Num]
        )
)

My visualizations look like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Anonymous]

@v-eqin-msft thank you for the detailed answer. However, I think I forgot to mention that the A, B, C etc are the calculated measures which are used in the values and hence can't create a table as you have shown.

[amitchandak]

@Anonymous , You can get previous day and MTD using time intelligence and Date table. You Display is Matrix Show on Row

Day

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Date Table -https://youtu.be/Qt0TM-4H09U

Display - USe show on row - refer https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

@amitchandak I will try this out. That would mean I would have to create two measures for each metric right? 

With respect to my example above, there would be a a measure for previous day A and MTD A, previous day B and MTD B and so on?

Also if I do this would I get a view as shown in my example? Because my example shows column name as metrics and in rows I have date and month, but after putting values in rows I would not get metrics as my column names. Is my understanding right?

[Greg_Deckler]

@Anonymous - Check out Measure Totals the Final Word as you can have a different calculation for the Total line versus row. 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907#M239

Also, see MM3TR&R - https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-Roll/m-p/411443#M150