Forum Discussion

ben_w's avatar
ben_w
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's avatar
    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 )
ABC11's avatar
ABC11
Icon for Resolver I rankResolver 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's avatar
    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....