topic Re: Monthly Report with Last Month's Variance Only in DAX Commands and Tips

Monthly Report with Last Month's Variance Only

Anonymous — Mon, 10 Feb 2020 20:34:41 GMT

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:

Here's what I currently get:

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]

Re: Monthly Report with Last Month's Variance Only

Stachu — Wed, 19 Feb 2020 08:56:02 GMT

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

Re: Monthly Report with Last Month's Variance Only

Anonymous — Wed, 19 Feb 2020 15:44:14 GMT

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

Re: Monthly Report with Last Month's Variance Only

Stachu — Thu, 20 Feb 2020 09:37:05 GMT

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

Re: Monthly Report with Last Month's Variance Only

Anonymous — Thu, 20 Feb 2020 15:02:59 GMT

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!

Re: Monthly Report with Last Month's Variance Only

Stachu — Thu, 20 Feb 2020 16:04:06 GMT

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

Re: Monthly Report with Last Month's Variance Only

Anonymous — Fri, 21 Feb 2020 14:40:05 GMT

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