Forum Discussion

jason435's avatar
jason435
Icon for Helper II rankHelper II
8 years ago
Solved

Year over year running total

I have successfully created a running total area chart.   ToDate Sum Revenue = CALCULATE( sum(Transactions[Revenue]), FILTER ( ALLSELECTED ( 'Calendar'), 'Calendar'[Date] <= M...
Need Help
  • jason435's avatar
    jason435
    8 years ago

    Thanks! This worked for me

    ToDate Sum Revenue LY = 
                     VAR mindate = 
			CALCULATE ( MIN ( 'Calendar'[Date] ), 
			ALLSELECTED ( 'Calendar'[Date] ) )
			RETURN
			CALCULATE(
			sum(Transactions[Revenue]),
			FILTER (
			ALL ( 'Calendar'),
			DATEADD('Calendar'[Date],1,YEAR) <= MAX ('Calendar'[Date])
			&& DATEADD('Calendar'[Date],1,YEAR) >= mindate
			))

    I had to put your calculation as a VAR otherwise it wouldn't work. 

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

Assuming that:

 

  1. There is a elationship from the Date column of your base data to the date column of your calendar table; and
  2. In your calendar table, you have extracted months and years from the date column using FORMAT(Calendar[Date],"mmmm") and YEAR(Calendar[Date]) formulas
  3. You have dragged Year and months from the calendar table to the X-axis of the area chart visual, try this

Try these measures

 

Revenue=SUM(Transactions[Revenue])

YTD revenue=CALCULATE([Revenue],DATESYTD(Calendar[Date],"31/12"))

YTD revenue in same period last year=CALCULATE([YTD revenue],SAMEPERIODLASTYEAR(Calendar[Date]))

 

Drag the last 2 measures to the visual

 

Hope this helps.