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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ADSL
Post Prodigy
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:

 

2022-10-02_23-05-38.jpg

 

Please kindly advise/suggest the measure of calculation. 

 

Source: https://docs.google.com/spreadsheets/d/1V28Rc2MouItZYWdddk-Gfp9hZoUIvHAp/edit?usp=sharing&ouid=10879... 

 

2022-10-02_22-27-46.jpg

 

Best Regards,

 

1 ACCEPTED SOLUTION

@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"))

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

@ADSL Maybe:

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

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.

 

2022-10-02_23-28-48.jpg

Hi @Greg_Deckler ,

 

Thank you for your helpful feedback.

 

Any suggestion if we follow the example result below?

 

2022-10-02_23-05-38.jpg

@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"))

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

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.

 

2022-10-03_00-23-47.jpg

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

May I need your help again the metric of Categories? 

 

2022-10-03_22-52-17.jpg

 

Best Regards,

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

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,

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

Category CategorySort
Morning 1
Lunch 2
Afternoon 3


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

Thank you for your helpful feedback!

 

Best Regards,

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors