Forum Discussion
Post PatronDATESMTD less one day problem
Hi,
I have a month slicer which users can select, then a measure using DATESMTD to capture all sales in the chosen month. However I now have a requirement to capture DATESMTD minus 1 day. So if a user selects June 2020, I needs the sales for 31st May to 29th June (everything shifted back one day). I don't think I can use DATESMTD anymore since this will always exclude May. Does anyone have an idea how this can be achieved please? Below is the measure, thanks!
5 Replies
- DavidWaters100
I think I solved it per below!
Sales = Calculate(sum(Sales[Amount]),DATESBETWEEN('Calendar Table[Date],min('Calendar Table'[Date])-1,max('Calendar Table'[Date])-1), Sales[Status]="Type A")- DavidWaters100
Ah - I also have a YTD measure which needs to be minus one too. Anyone have a solution for this please? I can't use Min and Max because that just affects the month selected, I need to open out to the whole year less one day! Below measure:
Sales YTD = (calculate(sum(Sales[sales]),DATESYTD('Calendar Table'[Date]),Sales[Type]="Type A")
So I need above to be 31/12/2019 to 29/06/2020, when a user selects June 2020
thanks!
- amitchandak
Super User
DavidWaters100 , first one should work
Sales= Calculate(sum(Sales[Amount]),DATESMTD(dateadd('Calendar Table'[Date],1,day)), Sales[Status]="Type A")If not try
Sales= Calculate(sum(Sales[Amount]),DATESMTD(dateadd('Calendar Table'[Date],-1,day)), Sales[Status]="Type A")
- DavidWaters100
Hi amitchandak,
Thank you for your reply - this should work for the YTD too (I posted a second reply to my own post as I realised I had a problem with YTD too).
I will give it a try
- v-diye-msft
Community Support
If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!
