calculate(countrows( "with a time"

[gmazza76]

Good afternoon,

I am trying to use current and past data to check call volumes between a set time frame and am having an issue as follows.

I can get the volumes from my data as per the DAX formula below per day, but I have now been asked to include a to and from time.
The formula from today is looking at the Monday of the current week which is great, but I am unsure how I would include the time part of the query.
My [Date] format is "13/12/2020 23:58:54" and I am unsure how to include the last part. I have seen time as "TIME(08,00,00)" but need some pointing in the right direction

=CALCULATE(COUNTROWS(Data),filter(Data,Data[CallType]=All_Data[CallType]),All_Data[Date]=today()-WEEKDAY(TODAY(),3))

thanks in advance
Gavin

 

[gmazza76]

this may sound daft, but I need to be able to look at times between 20:00:00 and 08:00:00 of the following day.

 

[RoryA]

I think you'd just add something like:

OR(HOUR(All_Data[Date])<8,HOUR(All_Data[Date])>=20)

 

[gmazza76]

thanks @RoryA,

The issue I have is that I need to go from 8pm the previous day to the 8am of the next day.
Unsure of how to amend the formula to look from 13/12/20 20:00 - 14/12/20 08:00

Would it be something like

=CALCULATE(COUNTROWS(Data),filter(Data,Data[CallType]=All_Data[CallType]),All_Data[Date]=today()-WEEKDAY(TODAY(),3-1),or(Hour(All_data[Date]>20),
filter(Data,Data[CallType]=All_Data[CallType]),All_Data[Date]=today()-WEEKDAY(TODAY(),3),or(Hour(All_data[Date]<08),

 

[RoryA]

I guess the filter would be something like:

OR(AND(All_Data[Date]=today()-WEEKDAY(TODAY(),3)-1,Hour(All_data[Date])>=20),AND(All_Data[Date]=today()-WEEKDAY(TODAY(),3),Hour(All_data[Date])<08))

as the additional date and time criteria. So it's either the previous day and an hour >= 20, or it's the next day and an hour < 8.

 

[gmazza76]

Sorry, does the "OR" take over the "calculate(rows" and if so what is the difference?

 

[RoryA]

I mean something like this:

=CALCULATE(COUNTROWS(Data),Data[CallType]=All_Data[CallType]),OR(AND(All_Data[Date]=today()-WEEKDAY(TODAY(),3)-1,Hour(All_data[Date])>=20),AND(All_Data[Date]=today()-WEEKDAY(TODAY(),3),Hour(All_data[Date])<08)))

 

[gmazza76]

thanks for this @RoryA, but I am getting an error "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression".

Current formula with correct columns in is as follows

=CALCULATE(COUNTROWS(All_Data),All_Data[Intent]=Previous_Week_Shift[Intents],OR(AND(All_Data[Call Start Date]=today()-WEEKDAY(TODAY(),3)-1,Hour(All_Data[Call Start Date])>=20),AND(All_Data[Call Start Date]=today()-WEEKDAY(TODAY(),3),Hour(All_Data[Call Start Date])<08)))

Does it matter that the format of the [Call_Start_Date] is in "DD/MM/YY 00:00:00"
I do have this column split into 2 others [Date] & [Time] but to be honest google confirms everything to do with a date, but not with time.

thanks for the support and hopefully you will be able to give me the help to complete this

 

[gmazza76]

Right, I have rechecked and the error seems to be the "AND" part as the calculation to this point works giving the correct figure if I rework it as

=CALCULATE(COUNTROWS(All_NLCS_Data),filter(All_NLCS_Data,All_NLCS_Data[Intent]=Previous_Week_Shift[Intents]),All_NLCS_Data[Date]=today()-WEEKDAY(TODAY(),3)-1,Hour(All_NLCS_Data[Time])>=20)

and in another column I do

=CALCULATE(COUNTROWS(All_NLCS_Data),filter(All_NLCS_Data,All_NLCS_Data[Intent]=Previous_Week_Shift[Intents]),All_NLCS_Data[Date]=today()-WEEKDAY(TODAY(),3),Hour(All_NLCS_Data[Time])<=07)

I get the correct figures, just not together by the look of it.
Is there a way to combine both in the same column?

thanks

 

[RoryA]

OK, let's try a simpler alternative:

=CALCULATE(COUNTROWS(All_Data),All_Data[Intent]=Previous_Week_Shift[Intents],All_Data[Call Start Date]>=today()-WEEKDAY(TODAY(),3)-1+TIME(20,0,0),All_Data[Call Start Date]<today()-WEEKDAY(TODAY(),3)+TIME(8,0,0))