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 [,...
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.

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

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