MTD -1 Month can't work

[Asmoday1507]

Hi

I have a big problem with calculating Sales MTD -1 (to calculate MoM Change). I can't use TOTALMTD, because firstly my data end on 2022-02-19, totalmtd returns day from today(), and secondly I have to use RLS after that.

My measure to calculate Sales MTD is below:

SALES MTD = 
VAR maxcalendar = CALCULATE(MAX(_Calendar[Date]),ALL(_Calendar))
VAR y = SELECTEDVALUE(_Calendar[#Year])
VAR m = SELECTEDVALUE(_Calendar[#Month])
VAR d = DAY(maxcalendar)
VAR startdate = DATE(y,m,1)
VAR enddate = DATE(y,m,d)
RETURN
CALCULATE(
    Metrics[Sales],
    FILTER(_Calendar,
    _Calendar[Date] >= startdate && _Calendar[Date] <= enddate))

Code below works:

But I can't calculate correctly SALES MTD -1 month

My code:

SALES MTD -1M = 
VAR maxcalendar = CALCULATE(MAX(_Calendar[Date]),ALL(_Calendar))
VAR y = SELECTEDVALUE(_Calendar[#Year])
VAR m = SELECTEDVALUE(_Calendar[#Month])
VAR d = DAY(maxcalendar)
VAR startdate = DATE(y,m,1)
VAR enddate = DATE(y,m,d)
VAR enddateminusm = EOMONTH(enddate,-1)
VAR startdate2 = DATE(YEAR(enddateminusm),MONTH(enddateminusm),1)
VAR enddate2 = DATE(YEAR(enddateminusm),MONTH(enddateminusm),d)
VAR result = 
CALCULATE(
    Metrics[Sales],
    FILTER(_Calendar,
    _Calendar[Date]>= startdate2 && _Calendar[Date] <= enddate2))
RETURN
result

 EOMONTH is used to get a date one month back. Why does it not working?

 It returns a blank value.

Best Regards

[amitchandak]

@Asmoday1507 , Try measures like

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.