Forum Discussion

Frequent Visitor

8 years ago

Solved

dax count how many consecutive days

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 - ...

Need Help

Zubair_Muhammad

8 years ago

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 )

Zubair_Muhammad

Community Champion

8 years ago

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 )

Zubair_Muhammad

Community Champion

8 years ago

ben_w

See attached pbix file here

With your sample ata

ABC11

Resolver I

3 years ago

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
Not applicable
3 years ago
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....