Calculate totals, based on the present date and time

[Anonymous]

Good day all,

Hope someone can help me with the following.

Below is a table with the total truck moves per appointment time and shift.

What I am looking for is the following:

Calculate the total truck moves within a shift, based on the present date and time.

For example:

The present date and time is: 2022-04-19 06:45

This means the present time is within the 00:00 – 07:15 shift

Power Bi then calculates the total truck moves in that particular shift: 56

Thanks upfront for your help!

John

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to my understanding, you want to get the sum value when the current time is in the period of [shift].

I have built a data sample, and on my side current time is 2022/4/22 2:32 PM

Please try to create a flag measure for visual-level filter later:

Flag = 
var _now=HOUR(NOW()) *60 + MINUTE(NOW())
var _minShift= LEFT(MAX('Table'[shift]),5)
var _maxShift=RIGHT(MAX('Table'[shift]),5)
var _minSeconds= LEFT(_minShift,2) *60+RIGHT(_minShift,2)
var _maxSeconds= LEFT(_maxShift,2)*60+RIGHT(_maxShift,2)
return IF( _now>=_minSeconds && _now<=_maxSeconds && DATEDIFF(MAX('Table'[appointment_time]),TODAY(),DAY)=0 ,1,0)

Then calculate the sum:

Sum = 
var _t= SUMMARIZE(FILTER( ALLSELECTED('Table'),[Flag]=1),[truck movement])
return SUMX(_t,[truck movement])

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to my understanding, you want to get the sum value when the current time is in the period of [shift].

I have built a data sample, and on my side current time is 2022/4/22 2:32 PM

Please try to create a flag measure for visual-level filter later:

Flag = 
var _now=HOUR(NOW()) *60 + MINUTE(NOW())
var _minShift= LEFT(MAX('Table'[shift]),5)
var _maxShift=RIGHT(MAX('Table'[shift]),5)
var _minSeconds= LEFT(_minShift,2) *60+RIGHT(_minShift,2)
var _maxSeconds= LEFT(_maxShift,2)*60+RIGHT(_maxShift,2)
return IF( _now>=_minSeconds && _now<=_maxSeconds && DATEDIFF(MAX('Table'[appointment_time]),TODAY(),DAY)=0 ,1,0)

Then calculate the sum:

Sum = 
var _t= SUMMARIZE(FILTER( ALLSELECTED('Table'),[Flag]=1),[truck movement])
return SUMX(_t,[truck movement])

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hi @Anonymous , 

I looked closely to your solution and it works fine for me.

Thanks a lot!

John

[Anonymous]

New Measure = CALCULATE(SUM( truck movement), FORMAT(appointment time,"dd-mm-yyyy")=TODAY(), shift = "00:00-7:15")

[Anonymous]

hi @Anonymous  , 

I looked closely to your solution and it works fine for me.

Thanks a lot!

John

[Anonymous · Accepted solution]

Glad it helps. From there you design according to what your dashboard needed.

If you are displaying in Table format, You can remove Shift from the formula. Group by shift under column, and you will get your subtotal automatically.

Appreciate if you could mark as solution if this helps out.

[amitchandak]

@Anonymous , Try a new column like

new column =
var _date = datevalue([appointment_time])
var _shift =[shift]
return
sumx(filter(Table, datevalue([appointment_time]) = _date && [shift] = _shift ), [truck moves])

Hope you were looking for a new column

[Anonymous]

hi @amitchandak  , 

I looked closely to your solution and it works fine for me.

Thanks a lot!

John

[Anonymous]

Hi @amitchandak ,

Thanks for your reply.

Is it also possible add a formula, so power BI determines automatcally the correct shift, based on the present date and time?

Based on the table above (example):

The present (local date_time) is 19-04-2022 06:15

This means the outcome of the formula must be 56.

John

[amitchandak]

@Anonymous , Shift column needs to be created, if that is the question. In case you need formula for that please share logic