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

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 )

5 Replies

  • 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

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