the start date in Calendar function cannot be later than the end date

[Anonymous]

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 

[v-frfei-msft]

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

[Anonymous]

@v-frfei-msft, 

Thanks for reply , I have tried formual which you have modified but still it is not working for me some error coming.

[v-frfei-msft]

Hi @Anonymous,

Could you please share your pbix to me ?

Regards,

Frank