Forum Discussion

bugs84's avatar
bugs84
Frequent Visitor
8 years ago
Solved

Calc Column with next working day

Hello everyone, I need to find a way to a dax formula for calculated column in a Calendar table which computes the "next working day" in the same table. Basically i need to get the minimum DATE val...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    HI bugs84

     

    Hopefully this shall do the job

     

    ThisOrNextWorkingDay =
VAR NextWorkingDay =
    MINX (
        FILTER (
            TableName,
            TableName[Day] > EARLIER ( TableName[Day] )
                && TableName[IsWorkingDay] = "Y"
        ),
        TableName[Day]
    )
RETURN
    IF ( TableName[IsWorkingDay] = "N", NextWorkingDay, TableName[Day] )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

HI bugs84

 

Hopefully this shall do the job

 

ThisOrNextWorkingDay =
VAR NextWorkingDay =
    MINX (
        FILTER (
            TableName,
            TableName[Day] > EARLIER ( TableName[Day] )
                && TableName[IsWorkingDay] = "Y"
        ),
        TableName[Day]
    )
RETURN
    IF ( TableName[IsWorkingDay] = "N", NextWorkingDay, TableName[Day] )
    • michaelu1's avatar
      michaelu1
      Advocate II
      3 years ago

      Hi Zubair_Muhammad this worked great!

       

      I have a very large date table though and this seems to take quite some time to load, is there another way to do this for large date tables?

  • bugs84's avatar
    bugs84
    Frequent Visitor
    8 years ago

    It worked!!

    Actually since i have a few rows, i guess removing the "IF" and making the measure a little simpler can be fine too.

    Thanks a lot!!

     

    ThisOrNextWorkingDay =
    MINX (
        FILTER (
            ZDate;
            ZDate[Date] >= EARLIER ( ZDate[Date] )
                && ZDate[IsWorkingDay] = "S"
        );
        ZDate[Date]
    )

  • MichaelNight's avatar
    MichaelNight
    Frequent Visitor
    4 years ago

    I just joined the pwbi community to thank you for this. 5 years after posting and still a simple and valid solution.