Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])))
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?
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)]
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |