cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Post Prodigy

## Count the No. of Order in Time Tracking Report

Hi BI Community Team,

We have 02 kinds of working time & Break/lunch Time- Morning & Afternoon Time of sales rep that visit the customer.

For Morning Time --- Min/Max <12:00:00

For Afternoon Time --- Min/Max > 13:00:00

EFFCALL_IND "E" --- Order

And Break/Lunch is from 12:00:00 till 13:00:00, then we want to know how many orders do they recieve and place order in the morning, afternoon & lunch time?

I would like to calculate this table in a way that produces the following example result:

Best Regards,

1 ACCEPTED SOLUTION
Super User

@ADSL Forgot military time so the Afternoon and Lunch numbers were scuff. Also, the new versions factor in the "E". Seems correct now. You could combine into a single measure and use a SWITCH(TRUE()...) for example to determine whether to show morning, afternoon or lunch. I'm not sure where you are pulling "Morning Section" for example. But, theoretically you could just grab the max of whatever that is and check which section you are in, returning the appropriate number. Updated PBIX below sig.

``````Morning =
VAR __MaxTime = TIME(12,00,00)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) <__MaxTime && [EFFCALL_IND] = "E"))

Afternoon =
VAR __MinTime = TIME(13,0,0)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) >__MinTime && [EFFCALL_IND] = "E"))

Lunch =
VAR __MinTime = TIME(12,0,0)
VAR __MaxTime = TIME(13,0,0)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) <=__MaxTime && ([TIME_IN] - TRUNC([TIME_IN])) >=__MinTime && [EFFCALL_IND] = "E"))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
11 REPLIES 11
Super User

``````Morning =
VAR __MaxTime = TIME(12,00,00)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) <__MaxTime))

Afternoon =
VAR __MinTime = TIME(1,0,0)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) >__MinTime))

Lunch =
VAR __MinTime = TIME(12,0,0)
VAR __MaxTime = TIME(1,0,0)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) <=__MaxTime && ([TIME_IN] - TRUNC([TIME_IN])) >=__MinTime))``````

PBIX is attached below signature.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Prodigy

If we count the number of order manually in excel and there are only 192 orders. But your number result in pbix, it seem not correct.

Please kindly assist to check it again.

Post Prodigy

Any suggestion if we follow the example result below?

Super User

@ADSL Forgot military time so the Afternoon and Lunch numbers were scuff. Also, the new versions factor in the "E". Seems correct now. You could combine into a single measure and use a SWITCH(TRUE()...) for example to determine whether to show morning, afternoon or lunch. I'm not sure where you are pulling "Morning Section" for example. But, theoretically you could just grab the max of whatever that is and check which section you are in, returning the appropriate number. Updated PBIX below sig.

``````Morning =
VAR __MaxTime = TIME(12,00,00)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) <__MaxTime && [EFFCALL_IND] = "E"))

Afternoon =
VAR __MinTime = TIME(13,0,0)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) >__MinTime && [EFFCALL_IND] = "E"))

Lunch =
VAR __MinTime = TIME(12,0,0)
VAR __MaxTime = TIME(13,0,0)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) <=__MaxTime && ([TIME_IN] - TRUNC([TIME_IN])) >=__MinTime && [EFFCALL_IND] = "E"))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Prodigy

In excel, I can identify and simplify the morning & afternoon session as the formula below.

Morning Session ---

1. =MINIFS([Start_Time],[Start_Time],"<12:00:00")

2. =MAXIFS([Start_Time],[Start_Time],"<12:00:00")

Afternoon Session ---

=MINIFS([Start_Time],[Start_Time],">12:00:00")

=MAXIFS([End_Time],[End_Time],">12:00:00")

Any suggestion if we want to move into our pbix file?

Here is the result that I am doing in excel.

Super User

@ADSL I would just create a disconnected table with "Morning", "Lunch" and "Afternoon" in as rows. Call the column "Category". Use this as the first element in your Column hierarchy for your matrix visual. Then you could do this:

``````Count =
VAR Morning =
VAR __MaxTime = TIME(12,00,00)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) <__MaxTime && [EFFCALL_IND] = "E"))
VAR Afternoon =
VAR __MinTime = TIME(13,0,0)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) >__MinTime && [EFFCALL_IND] = "E"))
VAR Lunch =
VAR __MinTime = TIME(12,0,0)
VAR __MaxTime = TIME(13,0,0)
RETURN
COUNTROWS(FILTER('Daily_Visit',([TIME_IN] - TRUNC([TIME_IN])) <=__MaxTime && ([TIME_IN] - TRUNC([TIME_IN])) >=__MinTime && [EFFCALL_IND] = "E"))
RETURN
SWITCH(MAX('Categories'[Category]),
"Morning",Morning,
"Lunch", Lunch,
Afternoon
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Prodigy

May I need your help again the metric of Categories?

Best Regards,

Super User

@ADSL Those were just example table and column name that I choose, you need to use what makes sense for your model.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Prodigy

Well noted with thank. If we want to see the start/end time in the morning & afternoon, any suggestion of metric?

Example: Morning ===Start Time: 8h00AM - End Time: 11h50AM

Afternoon === Start Time: 13h20PM - End Time: 17h00PM

Best Regards,

Super User

@ADSL I would literally just create a 2 column, 3 row disconnected table:

 Category CategorySort Morning 1 Lunch 2 Afternoon 3

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Post Prodigy

Best Regards,

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors