Forum Discussion

elxaxe's avatar
elxaxe
Icon for Advocate I rankAdvocate I
8 years ago
Solved

LastWorkDate QlikView Function to Power BI

Hi,

 

I'm migrating a Qliview dashboard to MS Power BI but there are some functions that Power BI does not have. One that is being used in the QV dashboard is LastWorkDate(start_date, no_of_days [, holidays]). That function returns the last working day after adding no_of_days to the start_date. Example:

 

LastWorkDate('19/10/2017', 2) // Will return '23/10/2017' because the 20th and 21st of october are not working days.

 

Also, if you specify holidays period, those days woyld be taken as not working days.

 

I need to convert expressions like:

IF(LastWorkDate(SHIPPING_DATE, 4)) < Floor($(varToday), "OPEN", "CLOSED")

But after searching in the forums and the Internet I cannot figure out how to put that conditional in a DAX expression for a Calculated Column.

If only we could create our custom functions...

 

THANKS IN ADVANCE!

Need Help
  • elxaxe's avatar
    elxaxe
    8 years ago

    Hi,

     

    Instead of adding 4 days I've tried to add only 1 to the formula. But my surprise is that if use it for the day '23/10/2017', it returns '25/10/2017' instead of '24/10/2017'.

     

    I've found the solution:

     

    aux_LastWorkDate(SHIPPING_DATE) = 
CALCULATE(
    MIN(MyCalendar[Date]);
    FILTER(
        ALL(MyCalendar);
        MyCalendar[Date] >= (SHIPPING_DATE.[Date] + 1)
        && MyCalendar[isWorkDay] = 1
    )
)

    I don't know why this works as the date should is the same.

4 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi elxaxe,

     

    First, you'll need to create an individual and continuous Date table if you don't have it yet.

    Date = CALENDAR("2017/10/1","2017/12/31")

    And add a column to the Date table that is 1 for workdays and 0 for non-workdays:

    IsWorkday = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

    Then the DAX formula to convert LastWorkDate(SHIPPING_DATE, 4) in a calculate column should be like below. :smileyhappy:

    =
    VAR CURRENT_SHIPPING_DATE = SHIPPING_DATE
RETURN
    CALCULATE (
        MIN ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date]
                >= CURRENT_SHIPPING_DATE + 4
                && 'Date'[IsWorkday] = 1
        )
    )

     

    Regards

    • elxaxe's avatar
      elxaxe
      Icon for Advocate I rankAdvocate I
      8 years ago

      Thanks for the answer, v-ljerr-msft !

       

      Just a question from a noob like me: why are you using a variable? Wouldn't be the same if I write:

       

      =
    CALCULATE (
        MIN ( 'Date'[Date] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Date]
                >= SHIPPING_DATE + 4
                && 'Date'[IsWorkday] = 1
        )
    )
      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi elxaxe,

         

        Thanks for point that out! The formulas are the same, and it's simpler to use your formula in this scenario. It seems to be that I have been used to use the VAR functions to write DAX formulas. :smileylol:

         

        Regards