Problem with LY MTD Calculations

[fahadnazir]

Hi All,

I am using the following formula to calcuate LY MTD

LY MTD Inv Sales =  CALCULATE( [Total Inv Sales], DATESMTD( SAMEPERIODLASTYEAR(DimDate[Date]) ) )

Assuming today is 25th Mar 2020

The problem is this MTD calculation is also taking sales of dates greatar than 25th March 2019.

How can i exclude these days from my calculation.

Will appreciate any help !

Thx

Fahad

[amitchandak]

You can try like

last year MTD Sales = CALCULATE(SUM([Total Inv Sales]),DATESMTD(dateadd('DateDim'[Date],-12,MONTH)))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

[fahadnazir]

Thanks for responding Amit.

I am afraid i cant follow the your answer.

Isnt there an easy way of filtering out the unwanted  days of LY MTD (26-31st march 2019) by may be flagging dates in calendar table ?

Thx

Fahad

[v-easonf-msft · Accepted solution]

Hi , @fahadnazir 

Try measures as below:

LY MTD Inv Sales = CALCULATE(SUM('Table'[Value]), FILTER(DATESMTD( SAMEPERIODLASTYEAR('Date'[Date]) ),MONTH([Date] )<MONTH(TODAY()) || (MONTH([Date] )=MONTH(TODAY())&&day([Date] )<=day(TODAY()))))

Here is a demo.

Pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Demo is not available can you please reload it

[fahadnazir]

Bravo @v-easonf-msft 

Got what i wanted !!!!