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.
I am using a measure to calculate Cumulative Spend within a year, so 2018 should appear as a separate line and 2019 as a separate line.
However, please take a look at the results:
I am getting this peculiar result on the 2nd image, where i want to compare cumulative 2019 vs 2018
First of all, you have to have a proper Date table in the model that's connected to your [Transatction Date] in a 1:many fashion. I called it Dates. Then you have these two measures:
[Total Spend] = SUM ( 'Concur Report'[Expense Amount] ) [Cummulative Spend] = var __oneYearVisible = HASONEVALUE( Dates[Year] ) var __cummulativeSpend = if ( __oneYearVisible, CALCULATE( [Total Spend], DATESYTD( Dates[Date] ) ) ) return
__cummulativeSpend [Cummulative Spend LY] = var __cummulativeSpend = CALCULATE( [Cummulative Spend], SAMEPERIODLASTYEAR( Dates[Date] ) ) return __cummulativeSpend
These should work correctly. And you only have to select one year to see both lines. The rule is simple: Do not use date columns from your fact tables, they should be hidden. Use only properly engineered Date tables to slice your data by time periods.
By the way, you might need to tweak this to only show cumulatives up to the current month. For this, you'll need to add one more filter to the [Cummulative Spend] measure.
One last thing, DO NOT ever divide measure(s) by 100000 or anything like that. Measures should always return raw figures. The units and formatting are handled by the visuals themselves.
Best
Darek
Hi @Anonymous
Please see the below expression that cen be used if you have a date dimension "Calendar".
Sales Running Total Same period last Year = VAR x = CALCULATE( MAX( 'Calendar'[Date] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ) RETURN CALCULATE( [Sales], ISONORAFTER( 'Calendar'[Date] , x, DESC ) )
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 |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |