Measure: Sum of time is less than 24 hours in last 6 days

[HenryJS]

Hi all,

How can I add a measure to the below which states IF Sum of [Rest Time 2] is less than 24 hours in last 6 consecutive days then YES

[v-alq-msft]

Hi, @HenryJS 

Based on your description, I created data to reproduce your scenario. The pbix file is attacehd in the end.

Table:

You may create two calculated column and a meausre as below.

Calculated column:

Hour = HOUR([Rest Time 2])
Minute = MINUTE([Rest Time 2])

Measure:

Result = 
var _date = SELECTEDVALUE('Table'[Date])
var _workname = SELECTEDVALUE('Table'[Worker Name])
var totalhour = 
CALCULATE(
    SUM('Table'[Hour]),
    FILTER(
        ALL('Table'),
        [Worker Name]=_workname&&
        [Date]>=_date-5&&
        [Date]<=_date
    )
)
var totalminute = 
CALCULATE(
    SUM('Table'[Minute]),
    FILTER(
        ALL('Table'),
        [Worker Name]=_workname&&
        [Date]>=_date-5&&
        [Date]<=_date
    )
)
return
IF(
   totalhour+INT(DIVIDE(totalminute,60))<24,
   "Yes",
   "No"
)

Result:

Best Regards

Allan

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

[HenryJS]

@v-alq-msft  thank you.

Below it states "Yes" when there is no row for dates before 07/09/20. It should be "No" because there are no rows before that date.

Is it possible to do that?

Also, how can I add another measure similar to what you've done which returns the Total Rest Time for the last 6 days?

Appreciate your help

[v-alq-msft · Accepted solution]

Hi, @HenryJS 

You may modify the 'Result' measure as below. The pbix file is attached in the end.

Result = 
var _date = SELECTEDVALUE('Table'[Date])
var _workname = SELECTEDVALUE('Table'[Worker Name])
var totalhour = 
CALCULATE(
    SUM('Table'[Hour]),
    FILTER(
        ALL('Table'),
        [Worker Name]=_workname&&
        [Date]>=_date-5&&
        [Date]<=_date
    )
)
var totalminute = 
CALCULATE(
    SUM('Table'[Minute]),
    FILTER(
        ALL('Table'),
        [Worker Name]=_workname&&
        [Date]>=_date-5&&
        [Date]<=_date
    )
)
return
IF(
    _date=
    CALCULATE(
        MIN('Table'[Date]),
        FILTER(
            ALL('Table'),
            'Table'[Worker Name]=_workname
        )
    ),
    "No",
    IF(
        totalhour+INT(DIVIDE(totalminute,60))<24,
        "Yes",
        "No"
    )
)

And you can create the following measure to get the total rest time.

Total Rest Time = 
var _date = SELECTEDVALUE('Table'[Date])
var _workname = SELECTEDVALUE('Table'[Worker Name])
var totalhour = 
CALCULATE(
    SUM('Table'[Hour]),
    FILTER(
        ALL('Table'),
        [Worker Name]=_workname&&
        [Date]>=_date-5&&
        [Date]<=_date
    )
)
var totalminute = 
CALCULATE(
    SUM('Table'[Minute]),
    FILTER(
        ALL('Table'),
        [Worker Name]=_workname&&
        [Date]>=_date-5&&
        [Date]<=_date
    )
)
var h = totalhour+INT(DIVIDE(totalminute,60))
var m = MOD(totalminute,60)
return
IF(
    ISBLANK(h),
    IF(
        ISBLANK(m),
        "00"&":"&"00"&":"&"00",
        "00"&":"&m&":"&"00"
    ),
    IF(
        ISBLANK(m),
        h&":"&"00"&":"&"00",
        h&":"&m&":"&"00"
    )
)

Result:

Best Regards

Allan

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

[HenryJS]

@v-alq-msft Thanks - that's perfect and addresses all issues.

It looks as if (m) returns a single digit instead of double digit for minutes in some instances?

[CNENFRNL]

Hi, @HenryJS , you may want to author your measure in this pattern,

Test Sum Time =
IF (
    SUMX (
        DATESINPERIOD ( 'table'[Date], MAX ( 'table'[Date] ), -6, DAY ),
        'table'[Rest Time 2]
    ) < 24,
    "Yes"
)