Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
Solved! Go to 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.
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 |
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 |
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
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.
Thanks for the tips Statchu. I hadn't run across that SQLBI info graphic before. Very good stuff!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |