Dynamic if sum of cells in subsequent order by date is greater than...

[Anonymous]

Sorry for poor title, it was difficult to describe.

Here is some example data:

Date	Type	Hours	Long-term
04.05.2020	Work	8	0
05.05.2020	Absence	8	0
06.05.2020	Absence	8	0
07.05.2020	Work	8	0
08.05.2020	Absence	8	1
11.05.2020	Absence	8	1
12.05.2020	Absence	8	1
13.05.2020	Absence	8	1
14.05.2020	Work	8	0

I need to create a measure that says, 

Long-term = 

if Type "Absence" && Sum(Hours) > 40, 1, 0

But, they have to be in a subsequential date-order like in the  example above. 

If there is a type Work in between, the measure returns false

Date	Type	Hours	Long-term
04.05.2020	Work	8	0
05.05.2020	Absence	8	0
06.05.2020	Absence	8	0
07.05.2020	Work	8	0
08.05.2020	Absence	8	0
11.05.2020	Absence	8	0
12.05.2020	Work	8	0
13.05.2020	Absence	8	0
14.05.2020	Work	8	0

Is this possible? Here's a link to a .pbix file if you want to try and solve this. 

[Anonymous]

HI @Anonymous,

You can use the following calculated column formula to check the continuous range between two work date:

Column =
VAR _prev =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', [Date] < EARLIER ( 'Table'[Date] ) && [Type] = "Work" )
    )
VAR _next =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( 'Table', [Date] > EARLIER ( 'Table'[Date] ) && [Type] = "Work" )
    )
VAR continues =
    IF (
        _prev <> BLANK ()
            && _next <> BLANK (),
        CALCULATE (
            SUM ( 'Table'[Hours] ),
            FILTER (
                ALL ( 'Table' ),
                [Type] <> "Work"
                    && [Date] IN CALENDAR ( _prev, _next )
            )
        )
    )
RETURN
    IF ( [Type] = "Absence" && continues > 40, 1, 0 )

Regards,

Xiaoxin Sheng

[Anonymous]

v-shex-msft, I am having issues for each of the EARLIER ( 'Table'[Date] )

[Anonymous]

HI @Anonymous,

What issue did you face? Please share more detailed information or some dummy data to help us clarify your scenario.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

The .pbix file is found in the first post.

Using your measure, it was unable to refer to the data columns and the last Type:

[Anonymous]

HI @Anonymous,

My formula is calculated column, it not able to use in measure expression. If you want a measure version, you can try to use the below formula:

measure=
VAR currDate =
    MAX ( 'Table'[Date] )
VAR currType =
    SELECTEDVALUE ( 'Table'[Type] )
VAR _prev =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] < currDate && [Type] = "Work" )
    )
VAR _next =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Date] > currDate && [Type] = "Work" )
    )
VAR continues =
    IF (
        _prev <> BLANK ()
            && _next <> BLANK (),
        CALCULATE (
            SUM ( 'Table'[Hours] ),
            FILTER (
                ALL ( 'Table' ),
                [Type] <> "Work"
                    && [Date] IN CALENDAR ( _prev, _next )
            )
        )
    )
RETURN
    IF ( currType = "Absence" && continues > 40, 1, 0 )

Regards,
Xiaoxin Sheng

[Greg_Deckler]

Right, so I am not 100% on your requirement, but I do know that you will need something like Cthulhu at some point in trying to solve it. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211