Year over year running total

[jason435]

I have successfully created a running total area chart.

ToDate Sum Revenue = CALCULATE(
					sum(Transactions[Revenue]),
					FILTER (
						ALLSELECTED ( 'Calendar'),
						'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
							&& 'Calendar'[Date] >= date(year(TODAY()),1,1)
					))

Now I would like to overlap last years performance. I have this:

ToDate Sum Revenue LY = CALCULATE(
					sum(Transactions[Revenue]),
					FILTER (
						ALL ( 'Calendar'),
						'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
							&& YEAR('Calendar'[Date])< max ('Calendar'[Year])
							&& 'Calendar'[Date] >= date(year(TODAY())-1,1,1)
					)
)

That doesn't work. See picture. Ideally, it would go bottom left to top right like the others. Update based on page filters.

[Ashish_Mathur]

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.

[Zubair_Muhammad]

Hi @jason435

Try this one

ToDate Sum Revenue LY =
CALCULATE (
    SUM ( Transactions[Revenue] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date]
            <= IF (
                HASONEVALUE ( 'Calendar'[Date] ),
                SAMEPERIODLASTYEAR ( VALUES ( 'Calendar'[Date] ) )
            )
            && 'Calendar'[Date]
                >= DATE ( YEAR ( TODAY () ) - 1, 1, 1 )
    )
)

[Zubair_Muhammad]

HI @jason435

Another way.

ToDate Sum Revenue LY =
CALCULATE (
    SUM ( Transactions[Revenue] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date]
            < MAX ( 'Calendar'[Date] ) - 365
            && 'Calendar'[Date]
                >= DATE ( YEAR ( TODAY () ) - 1, 1, 1 )
    )
)

[jason435]

Ignore what I said before... So I came up with this:

ToDate Sum Revenue LY = 
			CALCULATE(
				sum(Transactions[Revenue]),
				FILTER (
				ALL ( 'Calendar'),
				DATEADD('Calendar'[Date],1,YEAR) <= MAX ('Calendar'[Date])
				&& DATEADD('Calendar'[Date],1,YEAR) >= date(year(today()),1,1)
				))

Seems to work for YTD. But I have a date slicer on the page and I would like to replace the '>= date(year(today()),1,1)' for the beginning date with something that updates based on the date slicer. I have tried >= MIN ('Calendar'[Date]) but that doesn't work because it takes MIN for that day, and not MIN for the entire filtered date range on the page. Keep in mind this is a running total. Thanks in advance!

[Zubair_Muhammad]

@jason435

may be

CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )

[jason435 · Accepted solution]

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. 

[jason435]

Thanks, while an option, I have an extra twist.  if you look closely, the X axis is date. and will adjust depending on filter. If I only put month name number on X, when I choose only one month, I will not to do mouse over and see exact to the day of the month revenue, budget, LY.

I'm thinking if I do an extra column for on the calendar table, add 1 year to each date, and then do the running total based on that column, maybe will work. Will post back. Thanks for help!