Monthly Report with Last Month's Variance Only

[Anonymous]

Hello!

I'm certain this question has been asked a million times but all of my searching brings up the 1,000's of requests for a simple MoM variance. I've got that part handled. I'd like to show a more streamlined matrix though with several months of data with variance only for the prior 2 months. 

I'm trying to build a report that looks like this:

Ideal output

Here's what I currently get:

Current Output

Is there an "IsFiltered"-type pattern that will let me only show the variance once for the current month and supress it for all prior periods? Alternatively, is there a way to leave the calcs all the same but visually hide the prior month variances?

Or, since I'm a finance/Excel "guy", maybe I should be showing this in some other more visual way and simply recreating my Excel Pivot Table in Poewr BI is just plain lazy...

Thanks in advance!

Here's the DAX:

 Current month

Invoices By Category (w Pmts) = 
VAR
    InvoiceAmt = [Total Invoice Amount] + [Total Pmts Revenue]
RETURN
    IF(
        [BrowseDepth] > [MaxNodeDepth] + 1,
        BLANK(),
        IF(
            [BrowseDepth] = [MaxNodeDepth] + 1,
            CALCULATE(
                InvoiceAmt,
                FILTER(
                    VALUES( Items[IsLeaf] ),
                    Items[IsLeaf] = FALSE
                )
            ),
            InvoiceAmt
        )
    )

Last Month:

Invoices By Category (Last Month) = 
    CALCULATE(
        [Total Invoices By Category],
        PREVIOUSMONTH('Calendar'[Date])
    )

Variance:

MoM Invoice & Pmts Variance = [Invoices By Category (w Pmts)] - [Invoices By Category (Last Month)] - [Pmts Revenue Last Month]

[Stachu]

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

[Anonymous]

Sure! Data looks something like this (Product ID links to product table, date to calendar, etc). Trying to show MoM change only for the most recent 2 months WITHOUT showing it for all prior months. Is this possible? Screenshot of desired and current outputs are in the original post above. Thanks in advance for any help!

ProductID	TrnsDate	Amount
Product 1	10/31/2019	500
Product 1	11/30/2019	600
Product 1	12/31/2019	1000
Product 2	10/31/2019	500
Product 2	11/30/2019	600
Product 2	12/31/2019	200
Product 3	10/31/2019	500
Product 3	11/30/2019	500
Product 3	12/31/2019	500

[Stachu]

hmm, that table looks overly simplified when looking at the DAX you're using in other measures
This should work here

Var Last 2 =
VAR __NrOfMonthsBack = 2
VAR __MaxDate =
    EDATE (
        CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED () ),
        - __NrOfMonthsBack
    )
VAR __CurrentDate =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        __MaxDate >= __CurrentDate,
        BLANK (),
        SUM ( 'Table'[Amount] )
            - CALCULATE ( SUM ( 'Table'[Amount] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
    )

EDIT
ha, I thought that if measure returns blanks it wouldn't render in Matrix, but it seems that's not the case, so I guess it doesn't help you
you may look at the Custom Visuals https://appsource.microsoft.com/en-us/marketplace/apps?page=1&src=office&product=power-bi-visuals, but unfortunately I'm not sure there will be something suitable for you there 

[Anonymous]

Taking a step back, any thoughts/tips for showing MoM variance? Like I said in my original post, I'm a long-time Excel user who's still fairly new to PBI. I often find myself clicking the "Matrix" button and trying to recreate a Pivet Table.

What's a better, more PBI friendly way, to show MoM variance for something like customer spend?

Either way, thank you so much for trying to help!

[Stachu · Accepted solution]

What's the purpose of the dashboard, who is the user? Here is a reference for different visuals and where to use them:

https://www.sqlbi.com/wp-content/uploads/videotrainings/dashboarddesign/visuals-reference-may2017-A3...
From my experience it's always best to ask th user what information they need, and then start from there, visualizing to fit the purpose.

As for this case - is it always last 3 months? If that's the case then you could actually build few measures calculating relative to your max date, e.g. LastMonth-2, LastMonth-1, LastMonth, Last 3 Months, Var LM vs LM-1, etc.

[Anonymous]

Thanks for the tips Statchu. I hadn't run across that SQLBI info graphic before. Very good stuff!