Forum Discussion

DavidWaters100's avatar
DavidWaters100
Icon for Post Patron rankPost Patron
6 years ago

DATESMTD 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...
Need Help
DavidWaters100's avatar
DavidWaters100
Icon for Post Patron rankPost Patron
6 years ago

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's avatar
DavidWaters100
Icon for Post Patron rankPost Patron
6 years ago

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!