cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_J_England
New Member

CALCULATE between two times across multiple dates

Hello,

 

I am new to the world of Power Pivot / Power Query, Power BI, and data models - so please forgive me if my terminology isn't correct! 

 

I have a data model in Power Pivot which consists of two data sets. One called 'Combined' which lists a very large number of rows of transactions. Another called 'Tariffs' which lists a number of rows of (what I will refer to as) purchase periods. Each purchase period includes some text, date, time, and decimal number fields, but essentially these all amount to an ID number, start date, end date, start time, end time, start amount, end amount.

 

What I am trying to do, is create a DAX function which checks the data within the 'Combined' data set, against the adjacent fields within the 'Tariffs' data set, to count the number of rows where the ID matches, the purchase date falls between the start and end dates, the purchase time falls between the start and end times, and purchase amount falls between the start and end amounts.

 

Therefore, I created a calculated column within the 'Tariffs' data set and I am using CALCULATE to COUNTROWS of the 'Combined' data set using the adjacent cell values. Where text is concerned, I am using '=' and where a date, time, and decimal number is concerned, I am using '>=' and '<=' to create the range. Below is what I have:

 

=CALCULATE(COUNTROWS(Combined),FILTER(Combined,Combined[TerminalID]=Tariffs[TerminalID]

&&Combined[LocationName]=Tariffs[LocationName]

&&Combined[DayName]=Tariffs[DayName]

&&Combined[PurchaseStartDate]>=Tariffs[TariffStartDate]

&&Combined[PurchaseStartDate]<=Tariffs[TariffEndDate]

&&Combined[PurchaseAmount]>=Tariffs[TariffAmountStart]

&&Combined[PurchaseAmount]<=Tariffs[TariffAmountEnd]

&&Combined[PurchaseStartTime]>=Tariffs[TariffStartTime]

&&Combined[PurchaseStartTime]<=Tariffs[TariffEndTime]))

 

Now, everything works UNTIL I try to include the purchase time. Having looked at the data, it would appear (despite formatting) the time has a date attached still - the date being 31/12/1899.

 

Anyone got any ideas on how to remove the date associated with the time so I can check the same time range across multiple dates? For example, check all purchases between the hours 8am and 6pm across all days this year.

 

Many thanks for any help in advance.

 

Jake

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

Hi @J_J_England 

You can check that with:

[TimeColumnToCheck] >=  8*(1/24) &&  [TimeColumnToCheck] <  18*(1/24)

assuming TimeColumnToCheck is of type time, which seems the case given it shows that ancient date (day 0, or pre-Big Bang day, beginning of times for Power BI)

Dates are actually numbers internally, and the decimal part is the time. So 1/24 represents an hour.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

4 REPLIES 4
J_J_England
New Member

Hello @AlB,

 

Just following up on your comment, this issue has now been resolved. Whilst your solution wasn't exactly what I needed, it did inspire me!

 

Instead of formatting the start and end time as a date/time, I simply re-wrote the dataset so the start and end time was represented by a number ie 0-24. Which, in combination with your *(1/24), allowed the range to be calculated, but allow for the range to by dynamic based on adjacent cells!

 

Thanks for your help,

 

Jake.

AlB
Super User III
Super User III

Hi @J_J_England 

You can check that with:

[TimeColumnToCheck] >=  8*(1/24) &&  [TimeColumnToCheck] <  18*(1/24)

assuming TimeColumnToCheck is of type time, which seems the case given it shows that ancient date (day 0, or pre-Big Bang day, beginning of times for Power BI)

Dates are actually numbers internally, and the decimal part is the time. So 1/24 represents an hour.

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

Hello @AlB 

 

Thanks for the quick response!

 

This would work if the time range I needed to count purchases within was static, but in fact I need it to be dynamic based on the adjacent cell values. The range of start and end times in some cases can be 8am to 6pm, 7am to 7pm,  6pm to 6am (which would probably need to be split into 6pm to midnight, and midnight to 6am).

 

Any thoughts on how to get around this at all?

 

Thanks,


Jake

@J_J_England 

I don't understand. Can you provide an example based on data? And please show what would determine if the check is to be done with 8am to 6pm, 7am to 7pm, 6pm to 6am...

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors