Previous Day and Previous Day on Last Month, 2MAgo Measure Calculation

[Anonymous]

Hi,

Use the previous day of last month's measure, I did not get the value of Feb 28 in the table. Please see the below DAX formula. Please guide me on what is wrong on my end.

DAX Formula:

PreviousDay= calculate([Balance], filter(all('Calendar'), 'Calendar'[Date]= max('Calendar'[PreviousDate])))

PreviousDayLastMont =

var SelectYear = if(HASONEVALUE('Calendar'[Year]),values('Calendar'[Year]))

var SelectMonth = if(HASONEVALUE('Calendar'[FiscalMonthNum]),values('Calendar'[FiscalMonthNum]))

var SelectDay = day(CALCULATE(LASTNONBLANK('Calendar'[Date], [PreviousDay]),DATEADD('Calendar'[Date],-1,MONTH)))

Var PMDate = date(SelectYear,SelectMonth,SelectDay)

var _maxdate = max('Calendar'[Date])

var PMBalance = calculate([PreviousDay], DATEADD('Calendar'[Date],-1,MONTH))

var PMonthBal = if(_maxdate <= PMDate, PMBalance, blank())

return PMonthBal

PreviousDay2MAgo =

var SelectYear = if(HASONEVALUE('Calendar'[Year]),values('Calendar'[Year]))

var SelectMonth = if(HASONEVALUE('Calendar'[FiscalMonthNum]),values('Calendar'[FiscalMonthNum]))

var SelectDay = day(CALCULATE(LASTNONBLANK('Calendar'[Date], [PreviousDay]),DATEADD('Calendar'[Date],-2,MONTH)))

Var PMDate = date(SelectYear,SelectMonth,SelectDay)

var _maxdate = max('Calendar'[Date])

var PMBalance = calculate([PreviousDay], DATEADD('Calendar'[Date],-2,MONTH))

var PMonthBal = if(_maxdate <= PMDate, PMBalance, blank())

return PMonthBal

[lbendlin]

Yes, that's how calendars work (for better or for worse).  What did you expect to get back? Did you take into account that "previous day last month"  likely fell on a totally different weekday?  Is that an issue?

How important is a month-over month comparison for your business?

[Anonymous]

Thanks. I found the solution by changing the below code.

PreviousDayLastMont =

var SelectYear = if(HASONEVALUE('Calendar'[Year]),values('Calendar'[Year]))

var SelectMonth = if(HASONEVALUE('Calendar'[FiscalMonthNum]),values('Calendar'[FiscalMonthNum]))

var SelectDay = day(CALCULATE(max('Calendar'[Date]),DATEADD('Calendar'[Date],-1,MONTH)))

Var PMDate = date(SelectYear,SelectMonth,SelectDay)

var _maxdate = max('Calendar'[Date])

var _maxDay = day(max('Calendar'[Date]))

var PMBalance = calculate([PreviousDay], DATEADD('Calendar'[Date],-1,MONTH))

var L1MBal = calculate([Balancel], DATEADD('Calendar'[Date],-1,MONTH))

var PMonthBal =

if (_maxDay > SelectDay, if(_maxDay <= SelectDay, PMBalance, if(day(max('Calendar'[PreviousDate])) = SelectDay, L1MBal, blank())) ,if(_maxDay <= SelectDay, PMBalance, blank()))

return PMonthBal

Yes, it is our business requirement.

I calculate the average of the previous day for the last 3 Months (PD LM + PD 2MAgo + PD 3MAgo)/3.

To Calculate Rolling Forecast = Current Balance x [Average (LM + 2MAgo + 3MAgo) / Average (PD LM + PD 2MAgo + PD 3MAgo)]