Line Not Showing Month Totals for Year Before

[Anonymous]

Hello all,

So I have year over year cumulative totals showing on a Line Chart and I cannot figure out how to show all month totals for previous years even though we are only in August. I would like to see the whole year's months so I can see how we did last year during November for example. Is there anything I can do here? 

Thank you!

[v-xicai]

Hi @Anonymous ,

You may create calendar table first of all, create relationship between your data table and calendar table on date field, then create measures like DAX below.

DateDim= CALENDARAUTO()
 
This Year= TOTALYTD(SUM(Table1[Sales]),'DateDim'[Date])
 
Last Year = CALCULATE(SUM(Table1[Sales]),DATEADD('DateDim'[Date],-1,YEAR))
 
2 Year ago = CALCULATE(SUM(Table1[Sales]),DATEADD('DateDim'[Date],-2,YEAR))
 
3 Year ago = CALCULATE(SUM(Table1[Sales]),DATEADD('DateDim'[Date],-3,YEAR))
 
4 Year ago = CALCULATE(SUM(Table1[Sales]),DATEADD('DateDim'[Date],-4,YEAR))

Or could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Amy,

I have figured out my issue. Because my calendar table did not have any dates past today's date, the chart would not show future dates. The red line is still cummulative but it becomes constant because there has been nothing sold past the current date. Thank you for your help!

[Anonymous]

Hello Amy, 

Thank you for your response, I was creating a PowerBI with example data to show you the issue I am having and it set up perfectly with no issues. It is showing the whole previous year cumulative total. I am going to rewrite my formulas and try again in my original doc today. I think I must have a filter on that is messing up the visual. 

Thank you for your help!

[Anonymous]

If I change the year to 2018 then all the data shows up for that year and the year before since there was data already for 2018, so I am thinking it's just because my calendar table is only up until today. 

[CheenuSing]

Hi @Anonymous ,

Can you load the pbix to Google or OneDrive and share the link to find a solution.

Cheers

CheenuSing