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

Cumulative Total Comparison between 2 years on Line chart

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.

Cumulative Spend Previous Yr (M) =
 
CALCULATE(
   SUM('Concur Report'[Expense Amount])/1000000,
     FILTER(
        ALLSELECTED('Concur Report'),
        'Concur Report'[Transaction Date]<=MAX('Concur Report'[Transaction Date])
        &&YEAR('Concur Report'[Transaction Date])=[Previous Year]
     )
)
 
(Another similar measure for Current year)

 

However, please take a look at the results:

Capture.PNGCapture2.PNG

I am getting this peculiar result on the 2nd image, where i want to compare cumulative 2019 vs 2018

2 REPLIES 2
Anonymous
Not applicable

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

Mariusz
Super User II
Super User II

Hi @Renegade1 

 

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 )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

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