Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have prepared below formula for Age Days calculation previously it is working fine. when I have connected dashboard with Production database I am getting an error in this formula: the start date in Calendar function cannot be later than the end date. I have gone through other solution but how to incorporate within Age Days formula guide me here.
Age Days =
IF (
'Remedy SmartView'[Hrs Type to Use] = "Business",
IF (
'Remedy SmartView'[Sev-Pri] IN { "P4","Low","NV" }
&& [Corrected Resolution Date] <> BLANK (),
[NetWorkDays]*9/24,
([Current Date] - [Corrected Submitted Date])*9/24
),IF('Remedy SmartView'[Sev-Pri] IN {"P1","P2","P3"} && [Corrected Resolution Date]<> BLANK(),[Corrected Resolution Date]-[Corrected Submitted Date],[Current Date]-[Corrected Submitted Date])
)
For the above formula, I have used and prepared this 4 formulas :
1) NetWorkDays =
VAR Calendar1 = CALENDAR(MAX('Remedy SmartView'[Corrected Submitted Date]),MAX('Remedy SmartView'[Corrected Resolution Date]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
2) Current Date = NOW()
3) Corrected Submitted Date = FORMAT(IF(ISBLANK('Remedy SmartView'[ID_NUMBER]),"-",IF(ISBLANK('Remedy SmartView'[Assigned_Date]),IF(ISBLANK('Remedy SmartView'[Opened_Date]),"#N/A", 'Remedy SmartView'[Opened_Date]), 'Remedy SmartView'[Assigned_Date])),"General Date")
4) Corrected Resolution Date = IF(ISBLANK('Remedy SmartView'[ID_NUMBER]),"-",FORMAT(IF(ISBLANK('Remedy SmartView'[Resolved_Date]),IF(ISBLANK('Remedy SmartView'[Closed_Date]),IF(ISBLANK('Remedy SmartView'[Last_Mod_Date]),"#NA",[Last_Mod_Date]),[Closed_Date]),[Resolved_Date]),"General Date"))
How to handle this error in Age Days formula: the start in Calender function cannot be later than the end date
Hi @Anonymous,
Based on my test, I can reproduce your issue here. As the eror message, I think the issue should be related to the values of MAX('Remedy SmartView'[Corrected Submitted Date]) and MAX('Remedy SmartView'[Corrected Resolution Date]). You can refer to the online document to check the CALENDAR function.
So you can have a try to update your formula as below.
NetWorkDays = VAR Calendar1 = CALENDAR(MAX('Remedy SmartView'[Corrected Resolution Date]),MAX('Remedy SmartView'[Corrected Submitted Date])) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) RETURN COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])
Regards,
Frank
Thanks for reply , I have tried formual which you have modified but still it is not working for me some error coming.
Hi @Anonymous,
Could you please share your pbix to me ?
Regards,
Frank
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |