dax count how many consecutive days

[ben_w]

Hi

I'm trying to calculate how many consecutive days a machine is planned on a project.

Data table example:

Date - MachineNr - ProjectNr

2018-02-01 - MACH1 - PROJ1

2018-02-02 - MACH1 - PROJ1

2018-02-03 - MACH1 - PROJ1

2018-02-04 - MACH1 - PROJ1

2018-02-05 - MACH1 - PROJ2

2018-02-06 - MACH1 - PROJ1

2018-02-07 - MACH1 - PROJ1

2018-02-08 - MACH1 - PROJ1

Expected result:

Date filter on (2018-02-04)

Measure: Consecutive days: 4

Date filter on (2018-02-06)

Measure: Consecutive days: 1     ( only the 2018-02-06 itselves)

Date filter on (2018-02-08)

Measure: Consecutive days: 3

I have tried to count the amount of rows where ProjectNr and MachineNr are the same, and the filtered date is always one higher than the Earlier date, with the following code, which always responds with 1.

=
CALCULATE (
    COUNTROWS ( 'FactPlanning' );
    FILTER (
        ALL ( 'FactPlanning' );
        'FactPlanning'[MachineNr]
            = EARLIER ( 'FactPlanning'[MachineNr] )
            && 'FactPlanning'[ProjectNr]
                = EARLIER ( 'FactPlanning'[ProjectNr] )
            && 'FactPlanning'[Date]
                = EARLIER ( 'FactPlanning'[Date] ) + 1
    )
)

I don't have the feeling i'm looking in the complete wrong direction, but i just can't seem to figure it out completely.

Any solutions?

Thanks

[Zubair_Muhammad · Accepted solution]

@ben_w

Try this MEASURE

Measure =
VAR starting =
    CALCULATE (
        MAX ( TableName[Date] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[MachineNr] ),
            TableName[Date] < SELECTEDVALUE ( TableName[Date] )
                && TableName[ProjectNr] <> SELECTEDVALUE ( TableName[ProjectNr] )
        )
    )
VAR Seriesstart =
    IF (
        ISBLANK ( starting ),
        CALCULATE (
            MIN ( TableName[Date] ),
            FILTER (
                ALLEXCEPT ( TableName, TableName[MachineNr] ),
                TableName[Date] < SELECTEDVALUE ( TableName[Date] )
            )
        )
            - 1,
        starting
    )
RETURN
    DATEDIFF ( Seriesstart, SELECTEDVALUE ( TableName[Date] ), DAY )

[ben_w]

This is working exactly as it should! Many thanks.

I've only had to change the code a little to work with my dimensions (which i for simplicity reasons didn't mention), and changed the "SELECTEDVALUE" function to "IF ( HASONEVALUE ( T[c] ), VALUES ( T[c] ), BLANK() )" to be able to use it in DAX for SSAS Tabular 2016.

[Zubair_Muhammad]

@ben_w

See attached pbix file here

With your sample ata

[ABC11]

Hello Zubair

I need help- I would like to "do No of consecutive day worked by employee"

I try to follow this code- I am getting error

"A single value for column 'WORKDATE' in table 'New LEM Facts' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

No_Of_Days = 
VAR starting =
    CALCULATE(
        Max('New LEM Facts'[WORKDATE]),
            FILTER(
                ALLEXCEPT('New LEM Facts', 'New LEM Facts'[CONTRACT_NO]),
                    'New LEM Facts'[WORKDATE]<SELECTEDVALUE( 'New LEM Facts'[WORKDATE])
            )
    )
VAR Seriesstart=
    if(
        ISBLANK(starting),
        CALCULATE(
            MIN('New LEM Facts'[WORKDATE]),
                FILTER(
                        ALLEXCEPT('New LEM Facts','New LEM Facts'[WORKDATE]),
                        'New LEM Facts'[WORKDATE]<SELECTEDVALUE('New LEM Facts'[WORKDATE])
                )
        )
            -1,
        starting
    )
Return
DATEDIFF(Seriesstart,SELECTEDVALUE('New LEM Facts'[WORKDATE]),DAY)

 Thanks

[Anonymous]

I have the exact same problem - and when trying to change SELECTEDVALUE with IF ( HASONEVALUE ( T[c] ), VALUES ( T[c] ), BLANK() ) it doesn't work....