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 h...
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.

     

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.