Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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 

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

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.

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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

Hi @Anonymous,

 

Could you please share your pbix to me ?

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.