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