Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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:

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

i

2 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

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

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

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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