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 )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity 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 )
    • 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.... 

  • ben_w's avatar
    ben_w
    Frequent Visitor
    8 years ago

    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.