Forum Discussion
MTD Previous
I am using SAMEPERIODLASTYEAR for my MTD Last Year calculation, however it returns Sales for the entire month of the previous year. So if I am reporting MTD sales for 17-08-2017, the MTD Last Year I ...
I have already trimmed my calendar dimension in my tabular to not include any future dates. My fact is not going to have future sales so I probably don't have to worry about that. Doing this has not fixed my problem.
Hey,
to answer similar questions I always use the following approach, maybe it seems a little complex, but I appreciate the degree of freedom it provides, the following measures uses the same number of dates in the previous month, for reasons of simplicity I skipped the treatment being on March 30 or March 31
SameNumberOfPeriodsPrevMonth =
var currentDay = DAY(MAXX(VALUES('Calendar'[Date]),'Calendar'[Date]))
var currentMonth = MONTH(MAXX(VALUES('Calendar'[Date]),'Calendar'[Date]))
var currentYear = YEAR(MAXX(VALUES('Calendar'[Date]),'Calendar'[Date]))
var startday = 1
var endday = currentDay
var startmonth = if(currentMonth = 1, 12, currentMonth-1)
var endmonth = if(currentMonth = 1, 12, currentMonth-1)
var startyear = if(currentMonth = 1, currentYear - 1, currentYear)
var endyear = if(currentMonth = 1, currentYear - 1, currentYear)
var startdate = DATE(startyear, startmonth, startday)
var endDate = DATE(endyear, endmonth, endday)
return
IF(NOT(ISBLANK(CALCULATE(SUM('FactWithDates'[Amount]))))
,CALCULATE(
SUM('FactWithDates'[Amount])
,DATESBETWEEN('Calendar'[Date], startdate, endDate)
)
,BLANK()
)The measure creates this output
Hope this helps
Regards
Worked like a charm. Dank u Tom! 🙂
