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 All,
Context: I am trying to compare the Current MAT with Previous MAT by month. The idea being that I would have a bar, or line, graph, showing 'current MAT' as of Aug 2018 compared to 'Previous MAT' as of Aug 2017.
So Far: I've been fiddling with a couple of different MAT formulae.
MAT Current Year = CALCULATE ( [Total Value], FILTER ( ALL ( 'Calendar'), 'Calendar'[Sequential Day Number] > MAX ( 'Calendar'[Sequential Day Number] ) - 365 && 'Calendar'[Sequential Day Number] <= MAX ( 'Calendar'[Sequential Day Number] ) ) )
And.
MAT Current Year V2 = CALCULATE ( SUMX( FILTER( 'table1','table1'[sales]), [Total Value] ), DATESINPERIOD( 'Calendar'[Date],LASTDATE ( 'Calendar'[Date]), -12, MONTH ) )
These are doing exactly what they are supposed to - calculate the MAT each month.
Problem: So, how do I go about displaying a 'previous MAT' side-by-side with the current? At first I tried the date criteria to be 'a year earlier' like so:
MAT Previous Year = CALCULATE ( [Total Value], FILTER ( ALL ( 'Calendar'), 'Calendar'[Sequential Day Number] > MAX( 'Calendar'[Sequential Day Number] ) - 730 && 'Calendar'[Sequential Day Number] <= MAX ( 'Calendar'[Sequential Day Number] ) - 365 ) )
But it does not come out with the desired results:
Is this an issue with formula or an issue with how I am displaying it?
Many thanks in advance!
Hi @Reigning_data,
You should add [dates] from calendar table into chart X-axis. Besides, please use SAMEPERIODLASTYEAR to calculate MAT in previous year.
Regards,
Yuliana Gu
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 |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |