cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dizzygoldfish
Frequent Visitor

Monthly Report with Last Month's Variance Only

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 outputIdeal output

Here's what I currently get:

Current OutputCurrent 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]

 

1 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.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

6 REPLIES 6
Stachu
Community Champion
Community Champion

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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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!

 

ProductIDTrnsDateAmount
Product 110/31/2019500
Product 111/30/2019600
Product 112/31/20191000
Product 210/31/2019500
Product 211/30/2019600
Product 212/31/2019200
Product 310/31/2019500
Product 311/30/2019500
Product 312/31/2019500

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 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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!

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.



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors