Forum Discussion

nadirS's avatar
nadirS
Icon for Helper I rankHelper I
5 years ago
Solved

Lookup a Date from a Date Table

I have two tables in Power BI Desktop - One is a date Table (Attached) and another one which has the Start Date and Time for an activity. The completion time for all tasks is 5 days. The date table has a column (Holidays) which gives me 0's for Weekends/Holidays and 1's for Working days. I want to be able to Calculate the 'Proposed Completion Date'.

So Basically taking the SLAStartwithHolidays Date from One table adding 5 days to it and providing the corresponding date from the date table.

For Example if the Date from SLAStartwihHolidays is 01/02/2020 adding 5 days (Sum of 1's from the Holidays Colum) should give me 1/08/2020. 

 

I cannot just add 5 days to the start date becuse that will not take into account work days / holidays. 

 Date Table: 

Data Table has the following Field: 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, nadirS 

    Try to  create calculated column as below:

    Rownumber =
VAR tab =
    FILTER ( ALL ( 'Table' ), 'Table'[Holiday] = 1 )
RETURN
    IF (
        [Holiday] = 1,
        COUNTX ( FILTER ( tab, [Date] <= EARLIER ( 'Table'[Date] ) ), [Holiday] )
    )
    Flag = [Rownumber] + 4
    Result = 
var _enddate=MAXX(ALL('Table'),'Table'[Date])-5
return
IF (
    'Table'[Holiday] = 1 &&'Table'[Date]<=_enddate,
    LOOKUPVALUE ( 'Table'[Date], 'Table'[Rownumber], 'Table'[Flag], [Date] ),
    BLANK()
)

     The result will show as below:

     

    Please check attached pbbix for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, nadirS 

    Try to  create calculated column as below:

    Rownumber =
VAR tab =
    FILTER ( ALL ( 'Table' ), 'Table'[Holiday] = 1 )
RETURN
    IF (
        [Holiday] = 1,
        COUNTX ( FILTER ( tab, [Date] <= EARLIER ( 'Table'[Date] ) ), [Holiday] )
    )
    Flag = [Rownumber] + 4
    Result = 
var _enddate=MAXX(ALL('Table'),'Table'[Date])-5
return
IF (
    'Table'[Holiday] = 1 &&'Table'[Date]<=_enddate,
    LOOKUPVALUE ( 'Table'[Date], 'Table'[Rownumber], 'Table'[Flag], [Date] ),
    BLANK()
)

     The result will show as below:

     

    Please check attached pbbix for more details.

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.