Cumulative Month Total YoY

[yasbos]

Hello, Team. I've been struggling with this. I need to calculate the cumulative month total for the current year and for the previous year and present them on the same graph. My problem is that when I present the data, somehow the graph shows data for future dates for the current month. I don't understand why. Today is the 6th of the month, so I have data until the 5th of the month. Therefore, the blue line should stop at the 5th. The numbers from the 1st until the 5th of the month are correct. However, I don't understand where it gets the numbers for the future dates. Thanks!

[ribisht17]

@yasbos 

Please cehck this one Solved: cumulative monthly total - Microsoft Power BI Community

Regards,

Ritesh

[yasbos]

Ok, now ... the plot thickens 🙂 I reviewed your referred solution, and I had everything set up properly. I even had tried several variations--all to no avail, and that's what was frustrating. Something strange happened just now, though: I have a measure, Current_Date, in my calendar table. What it does is it gets the max of the dates in my sales fact table, and that is the date that I used as a condition.

MaxDate=max(Sales_Fact[Sale Date])

calculate(Sales_Fact[Sales MTD],'Calendar'[Date]<=MaxDate)

That was not working. When I set MaxDate to equal today(), instead of max(Sales_Fact[Sale Date]), everything worked perfectly! I switch back to maxdate, it gives me these phantom numbers again for the remainder of the month. This is just kooky! Below is the correct result -- after I replaced maxdate with today(). 

[ALLUREAN · Accepted solution]

Hi, @yasbos 

Try this: 

MaxDate=CALCULATE(max(Sales_Fact[Sale Date]), REMOVEFILTERS('Calendar'[Date]))

[yasbos]

If anyone knows why it worked with removefilters, that would save me from the straightjacket. Thanks.

[yasbos]

OMG, @ALLUREAN ! That worked. I have NO IDEA what just happened, though. Thanks so much. Thanks to @ribisht17 , too, for responding.