Using Calendar Table to count records between two dates/times starting today and ending tomorrow

[MRGMSR]

We have an incidents database that records each time a vehicle is "EARLY", "LATE', and "NORMAL or On Time" to a certain bus stop and we need to determine the percentage of time vehicles are early, late or normal based on the transit day. The problem is the transit day falls between 3 a.m. (today) and 4 a.m. (tomorrow) and I don't know how to filter for that. I created a calendar table then created two fields that capture the Start of the Transit Day and the End of the Transit day but have no idea how to use it, yes, I am very new to this, my apologies. 

I have a filter that I found on this forum that works great to capture the incidents within the normal 24 hour day starting at midnight and ending at midnight but I need to capture incidents between today @ 3 am and tomorrow at 4 a.m. as the "transit day" and have no idea how to incorporate the calendar table with this measure (I'm hoping it's as easy as plugging it in but please let me know what I could do to get to the end result):

Sum if Early = CALCULATE(COUNT([incident_name]),FILTER(OTP_Report_SQL,SEARCH("Early",[incident_name],1,0))))

I have the same filter for "LATE" and "NORMAL"

Here are the two tables I am working off. Any help or direction would be greatly appreciated!

Thank you in advance, so much for your guidance!

[ChandeepChhabra · Accepted solution]

Hi @MRGMSR,

There can be 2 totals

1. Sum of (Early + Normal + Late)
   • =CALCULATE(COUNT([incident_name]),FILTER(OTP_Report_SQL,OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))
2. Sum of (Early + Normal + Late) for the complete table
   • =CALCULATE(COUNT([incident_name]),FILTER(ALL(OTP_Report_SQL),OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))

Please download the updated powerpivot file

[ChandeepChhabra]

Hi @MRGMSR

First I added a date column (to strip off time) in your "OTP_Report_SQL" table by using the formula =INT(OTP_Report_SQL[Incident_date_time])

I am hoping that you already have the relationship set up between "OTP_Report_SQL" table and Transit_Day_Time Table

Please modify your measure

SumifEarly=

CALCULATE(COUNT([incident_name]),OTP_Report_SQL[incident_name]="Early",FILTER(OTP_Report_SQL,OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))

Please check if it gives you the desired result. You can download a mock powerpivot file that I created

[MRGMSR]

Hi ChandeepChhabra,

Thank you SO MUCH for this! It worked but as it usually goes, now I have another problem. Now I need to get the percentage of the total number of incidents per transit day so I used your calculation to calculate the count of incidents (Early, Late, Normal) and tweaked it to calculate the (total) count of incidents per transit day (below) but when I try to create a measure to provide the percentage of the "Early", "Late", "Normal" incidents per transit day using the SumifEarly divided by the TotalIncidents2 measure:

(Sumifearly2 = CALCULATE(COUNT([incident_name]),OTP_Report_SQL[incident_name]="Early",FILTER(OTP_Report_SQL,OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))

/

TotalIncidents2 = CALCULATE(COUNT([incident_name]),OTP_Report_SQL[incident_name],FILTER(OTP_Report_SQL,OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))

I get the error below: 

MdxScript(Model) (18,75) Calculation error in measure 'OTP_REport_SQL'[TotalIncidents2]: Cannot convert value 'NORMAL' of type Text to type True/False.

Any ideas on what I should do? I am looking up this message online and on this forum to see if I can get a solution but if you know off the top of your head, I'd greatly appreciate your help once again!

Thank you

MRGMSR

 

[ChandeepChhabra · Accepted solution]

Hi @MRGMSR,

There can be 2 totals

1. Sum of (Early + Normal + Late)
   • =CALCULATE(COUNT([incident_name]),FILTER(OTP_Report_SQL,OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))
2. Sum of (Early + Normal + Late) for the complete table
   • =CALCULATE(COUNT([incident_name]),FILTER(ALL(OTP_Report_SQL),OTP_Report_SQL[Incident_date_time]>=RELATED(Transit_Day_Time[StartofTransitDay])&&OTP_Report_SQL[sched_time]>=RELATED(Transit_Day_Time[EndofTransitDay])))

Please download the updated powerpivot file

[MRGMSR · Accepted solution]

Hi ChandeepChhabra,

That did the trick!!! Thank you so much. I truly appreciate your working on this for me.

Thanks,

MRGMSR

[ChandeepChhabra]

@MRGMSR You are welcome!