MTD Last Month totalling entire month

[ciken]

I am working to build a measurement that looks at sameperiod last month and presents revenue total...but when I do it, I get the right information on each day except the last day on my date table shows the FULL revenue for the previous month. 

Here is my formula: 

MTD Last Month =

CALCULATE([Order Value (EP + Amazon)],DATEADD(DATESMTD(CalendarTable[Dates]),-1,MONTH))

And here's how it looks - everything is right by day 1,2,3,etc. But when it reaches today, it throws the full last month value in vs stopping at $214,688.

Any idea how to stop the revenue from just throwing the ENTIRE month in when it reaches the end?

[v-rzhou-msft · Accepted solution]

Hi @ciken 

Your issue should be caused by logic of your measure [Order Value (EP + Amazon)].

Here I have a test and I use sum function to get result in [MTD Last Month] expression. This code will work well.

MTD Last Month = 
CALCULATE(SUM(Sheet11[Value]),DATEADD(DATESMTD('Calendar'[Date]),-1,MONTH))

Result is as below. Today is 2021/11/19. We can get correct MTD in last month by this measure. 

Please chech the filter in [Order Value (EP + Amazon)]. Or you can share a sample with me to make it easier for me to understand your data model.

Best Regards,
Rico Zhou

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

[Greg_Deckler]

@ciken You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...