Reply
Highlighted
Member
Posts: 45
Registered: ‎04-06-2017

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

[ Edited ]

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 

Community Support Team
Posts: 1,310
Registered: ‎07-10-2018

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

Hi @jainravi,

 

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.

 

Capture.PNG

 

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

Member
Posts: 45
Registered: ‎04-06-2017

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

@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.

Community Support Team
Posts: 1,310
Registered: ‎07-10-2018

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

Hi @jainravi,

 

Could you please share your pbix to me ?

 

Regards,

Frank