MTD Total, Previous Day and Current Date Total

[Pbi07]

Hello 

I am trying to get the MTD Total along with the Total for previous day and total for Today for a simple fact table with Order Quantity. 

The user would like to look at this report daily.

I was thinking of using the slicer with the calendar Month. 

I tried using the DATEADD function with -1 for Yesterday total and with 0 for today, but gave an error when i try adding that to the visual. Is there a different way to achieve this? My model is attached. 

Prior Day Orders = CALCULATE(SUM(Orders[Quantity]),DATEADD('Calendar'[Date],-1,DAY))

Expected result is 

Customer	MTD Total	Yesterday Total	Today Total	Difference
100	2700	1200	0	-1200
101	2000

https://drive.google.com/file/d/1neGxPlFQPQI3O9BFSfwXvCStw5F4sREO/view?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Pbi07]

Thanks @Ashish_Mathur . 

With the 3 slicers - Year, Month & date  i tried to add the previous year MTD value and did not show the values for April 2020 even though the fact had an order qty of April 2020 for 1500. Anything that i am doing wrong? 

MTD LY Orders = TOTALMTD([Total orders],SAMEPERIODLASTYEAR('Calendar'[Date]))

[Ashish_Mathur · Accepted solution]

Hi,

Modify your Calendar to start from April 1 2020 and then this measure will work

Total MTD orders in SPLY = CALCULATE([Total orders],DATESBETWEEN('Calendar'[Date],EOMONTH(MIN('Calendar'[Date]),-13)+1,EDATE(MIN('Calendar'[Date]),-12)))

[Pbi07]

Thanks @Ashish_Mathur 

When i try to apply this in to the actual model i am getting incorrect  Last Year MTD totals. 

The calendar is from May 2018. Not a calendar issue. 

1. When i keep the 3 slicers, the last Year MTD measure is incorrect

2. When i keep Year and Month slicer and remove the date slicer the measure total is correct. but the previous day and the current day goes away as it is tied to the date

I tried with 2 measures and 

TEST 1 MTD LY = 

CALCULATE(SUM(Orders[Sales Qty]),DATESBETWEEN('Calendar'[Date],EOMONTH(MIN('Calendar'[Date]),-13)+1,EDATE(MIN('Calendar'[Date]),-12)))

TEST 2 MTD LY = TOTALMTD(SUM(Orders[Sales Qty]),SAMEPERIODLASTYEAR('Calendar'[Date]))

[Ashish_Mathur]

Hi,

First of all, let me know which slicers do you wan to keep - Year, Month and Days or only Year and Month?  Share the link from where i can download your revised file with Dates from 2018.  Clearly show the wrong result and also show me the exact result that you are expecting.

[Pbi07]

Thanks @Ashish_Mathur 

I will try with only the date slicer as requirement is to pick any date. 

I could not replicate that last year MTD issue in to this smaller model. I will find out. I am sure i am messing something up in that meaure. Thanks for your help.