Latest Enterprise DNA Initiatives

Problem of the Week #10- Dealing With Time Periods

Hi All,

I trust you are all keeping well and safe.

Welcome to the 10th instalment of the Enterprise DNA problem of the week.

Time is something that can cause a few hiccups in Power BI and isn’t always the easiest to manage, however the number of use cases spans across many industries and scenarios.

A great technique to be able to call upon and use in your development.

The ask this week is to calculate the number of customers between a specific date period and also define a start time and end time for the selected date period.

This is a DAX only challenge and the model is super simple as its all about focussing on the technique required.

Please find attached the PBIX file.

Any questions or clarifications do not hesitate to get in touch.

Good luck!
Haroon
POW #10 HA.pbix (1.2 MB)

5 Likes

All,

Also wanted to highlight the brief intro video to this problem that @haroonali1000 and I taped that provides some additional context and background on where this problem came from, and also at the end gives you slight peek at where @haroonali1000 is headed for next week’s Enterprise DNA Data Challenge.

If you’ve been hesitant to jump into participating in Problem of the Week, this is a great week to start. It’s an immensely practical problem that will add some valuable tools to your toolbox, and shouldn’t take too long to complete.

Finally a reminder to use the Hide Details or Blur Spoilers options when posting anything specific about your solution.

image

As always, thanks to Haroon for leading this week’s problem, and to all of you for participating in what we think is a really worthwhile community learning initiative.

  • Brian
2 Likes

Hello @haroonali1000,

Thank You for hosting this challenge. :slightly_smiling_face:

Here’s my solution -

Summary
Weekday Customers = 
VAR _Weekday_Customers = 
CALCULATE( SUM( 'Fact Customer Count'[CustomerCount] ) , 
    FILTER( 'Date' , 
        'Date'[Day Type] = "Weekday" ) )

VAR _Weekday_Customer_Ignoring_Blanks = 
IF( ISBLANK( _Weekday_Customers ) , 
    0 , 
    _Weekday_Customers )

RETURN
_Weekday_Customer_Ignoring_Blanks

To cross-check the results I also created an excel file. Below is the PBIX as well as Excel File attached as a solution.

Thanks and Warm Regards,
Harsh

Files -

Summary
1 Like

Hi @haroonali1000

This is my solution for POTW#10

Summary

image

POW #10 HA - jbressan.pbix (1.3 MB)

2 Likes

Hi @haroonali1000.

Here is my submission for POTW #10.
Greg

Summary

Here are the notes I kept during development:

NOTES
  • renamed [Date] table to “Dates”
  • marked [Dates] table as a date table
  • transformed data for [Dates] table, used “Choose Columns” to keep only [Date], [DayOfWeekName], and [IsWorkingDay] columns
  • added slicer for Dates[Date]
    • turned off selection header
    • turned on title and set title to “DATE”
    • set “Date Inputs” font colour to #FF0000 (red)
  • added what-if parameter for “Start Time” (0-24, step 1)
    • turned off selection header
    • turned on title and set title to “START TIME”
    • set “Numeric Inputs” font colour to #FF0000 (red)
  • added what-if parameter for “End Time” (0-24, step 1)
    • turned off selection header
    • turned on title and set title to “END TIME”
    • set “Numeric Inputs” font colour to #FF0000 (red)
  • added [Key Measures] table
  • added [Selected Start Datetime] measure
  • added [Selected End Datetime] measure
  • changed datatype of “Fact Customer Count[StartDateTime]” column from TEXT to DATETIME
  • added [Total Customers] measure
  • added Card to display [Total Customers] measure
  • added [Total Customers (Weekdays Only)] measure
  • added Card to display [Total Customers (Weekdays Only)] measure

Edit:

  • updated the logic in my [Selected End DateTime] measure to calculate hh:59:59 instead of my first try where I mistakenly used hh:00:00; now my results agree with @Harsh and @jbressan; updated my screenshot and PBIX

Selected End Datetime =
VAR _SelectedDate = MAX( Dates[Date] )
VAR _SelectedEndHour = SELECTEDVALUE( 'End Time'[End Time] )
VAR _SelectedTime = TIME( _SelectedEndHour, 59, 59 )
VAR _Result = _SelectedDate + _SelectedTime

RETURN
_Result

FINDINGS
  • there is data only for March 23-March 25, all of which are weekdays, so both “Total Customer…” measures return the same result in all cases
  • after I finished development, I looked at the screenshots (not the PBIXs) for @Harsh’s and @jbressan’s submissions, and saw that I have different (lower) numbers; I used what-if parameters for start and end time instead on the supplied [Time Table] table, so am guessing that may be the difference
    • as it turns out, I was in error as I was using hh:00:00 for an end time instead of hh_59:59; once fixed, my results agreed with @Harsh and @jbressan
    • I can’t stress how much my use of (always hidden, but I think I forgot this time) [Validation] pages help me to verify calculations and uncover issues; highly recommended

POTW #10 - Customers in Datetime Range - Greg Philps.pbix (1.2 MB)

1 Like

Hi @Greg

The difference is the approach to handle the minute part, my approach was to include from h:01 to h:59.

1 Like

Hello @Greg,

Really liked your approach. Saw something new here. :+1:

Summary

The reason why there was a difference between our numbers is becasue of the following reasons -

1. You divided the “Start Time” and “End Time” by 24 whereas in my case I considered them as a whole, that is, as it is.

2. You compared “StartDateTime” from the fact table with the “Start Date and Time” (measure) and “End Date and Time” measure whereas in my case I let the relationship took care of it.

3. Lastly, this simple measure would have done the trick here and matched our numbers -

Total Customers - Harsh = 
CALCULATE( SUM( 'Fact Customer Count'[CustomerCount] ) ,
    FILTER( 'Time Table' , 
        'Time Table'[Hour] >= SELECTEDVALUE( 'Start Time'[Start Time] ) &&
        'Time Table'[Hour] <= SELECTEDVALUE( 'End Time'[End Time] ) ) )

Below is the screenshot of the result provided for the reference -

Also attaching the working PBIX file just for the reference purpose.

Lastly, really liked your explanation on “Notes” and “Key Findings”. In the morning, when I uploaded the solution I also observed the same thing in terms of nature of the data and that’s why I also uploaded my cross-verification Excel file. So really spot on over there from you. :slightly_smiling_face: :+1:

Thanks and Warm Regards,
Harsh

Summary
2 Likes

Hello!
Here goes my solution for challenge number 10.

Spoiler

I used variables to advance & check along the way
I computed a min & max date time and added 1/24 as I wanted to include the customers for the Endtime hour selected.
I than created a virtual table to keep only the data I need and filter only the week days. A second variable takes into consideration the time interval.

image

POW #10 Alex Badiu.pbix (3.2 MB)

2 Likes

Here goes my solution for Week-10 and I’m really inspired by the inclusion of “Average customers per hour” by @jbressan

Not a very complex problem but, I loved the different approach taken by fellow participants.

Thanks, @haroonali1000 for the challenge. :slight_smile:

7 Likes

Hi All,

I trust you are keeping well and safe in these testing times.

A massive thank you to all of you who participated in Problem of the week #10.

As you will be able to testify the problem itself was on the easier side of the scale compared to some of the problems we’ve hosted in recent weeks.

Nonetheless there were some great entries across the board and some fantastic opportunities to learn from others. I definitely picked up a few things and even went down a little rabbit hole with Brian that I still don’t think I fully understood but that’s for another day.

This is a likely to be a very common pattern used in industry when end users want to specify start times and end times for particular periods.

Here is how I tackled this problem:

Step 1: Creating support table for the start time and end time. I just used the generate series function.

Step 2: A simple sum to get the total customers.

Step 3: Created a calculation considering the disconnected tables and harvest the selections into the calculation. Giving me the customers based on the date and time period selected.

Step 4: Creating a second measure but this time add the filter for weekday. Measure branched from the measure above and added a filter from the date table for weekday.

In general all entrants tackled the problem with the same high-level principles however there were some great intricacies in the implementations, and I would definitely check out the solutions posted in the forum.

Short and sweet .

Thanks all again for participating and thanks @BrianJ for hosting :slight_smile:

See my Pbix and video below.

POW #10 HA.pbix (1.3 MB)

P.S see you all on the challenge :grin:

Thanks,

Haroon

3 Likes

this is my solution

POW #10 Gharbi Abdelkader.pbix (1.3 MB) [spoiler]

Summary

This text will be blurred

[/spoiler]

1 Like

I don’t understand why we should use variables to select dates and times, I mean if we have good data model
we just need to add filter in the report (filter for days and filters for hour) write simple formula: sum (column count of customers)
and let filter context does the rest of the job
and for weekday using calculate to add the condition
thanks