Cumulative Total This Year vs Last

[Gary_R]

I have two measure created. Total Project Dollars for the current year and last year. I plot both of them on an area chart by date and it works perfectly. I then calculate cumulative totals for both. The code is here:

Project Cumulative Total = CALCULATE([Total Project], FILTER( ALLSELECTED('Goal Metrics'), 'Goal Metrics'[Dates] <= MAX('Goal Metrics'[Dates])))

The second calculates the on the total last year.

If I put either one on an area chart alone, it works fine. When I put both of them on together…one sits on top of the other and they accumulate. I have no idea what I am doing wrong. If I select just 2019, it filters out all of the 2018 data. I cant keep both of them on the chart.

2019 BI Goals.pbix (114.8 KB)

I don’t know how to upload the chart so I uploaded the PBIX. Driving me crazy…

[sam.mckay]

One thing to do here is always place formulas into tables first so you can see the results. This way you’ll know if it’s the formula which is the issue.

The issue you have here is simple…you don’t have a date table.

You must always have one. All time intelligence functions are optimized to work over date tables, not the dates within your fact tables.

See below.

http://portal.enterprisedna.co/courses/103686/lectures/1772347

I’ve added one in here, then created the relationship

Then change all your formulas over

Project 1 yr ago = CALCULATE([Total Project], DATEADD( Dates[Date], -1, YEAR))

Project Cumulative Total = 
CALCULATE([Total Project],
    FILTER( ALLSELECTED( Dates), Dates[Date]  <= MAX(Dates[Date])))


Project Cumulative Total LY = 
CALCULATE( [Project 1 yr ago],
    FILTER( ALLSELECTED( Dates), Dates[Date]  <= MAX(Dates[Date])))

Things start changing up a lot now with your results, as it should.

You must also make sure all the filters from dates come from the date table. For example…

Now your charts will now work as they should also.

I’ve attached.
2019 BI Goals.pbix (169.3 KB)

Thanks
Sam

[Gary_R]

Using this over and over now…thank you…