Forum Discussion

elxaxe's avatar
elxaxe
Advocate 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 [,...
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.

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft 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
Advocate 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
    Microsoft 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

    • elxaxe's avatar
      elxaxe
      Advocate I
      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.