Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return value if date is between 2 dates in another table

I have two tables, Calendar and Position, I'm trying to add a calculated column to Calendar that shows what department the NetID belonged to on that date.

 

Ex: It will find all the positions "a" had, then find the one where the "Date" is between "Start Date" and "End Date", and return the "Department".

 

Normally I would run 2 loops to get the answer, but not sure how to do this in Power BI as I'm quite new, please help thanks!

Calendar

DatesNetID
1/1/2020a
1/1/2020b
1/1/2020c
1/2/2020a
1/2/2020b
1/2/2020c
1/3/2020a
1/3/2020b
1/3/2020c

 

Position

NetIDStart DateEnd DateDepartment
a10/1/201912/1/2019Dining
a12/2/20193/1/2050Res life
b12/5/20193/1/2050Housing
c10/31/201811/5/2019Housing
c11/6/20193/1/2050Dining
Need Help
  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    I second the approach by JarroVGIT , but I would drop the fact and expand the table as he/she mentioned. Then define the relationship with Dates and the new fact.

     

    Refer to these posts.

     

    https://community.powerbi.com/t5/Desktop/Calculate-Sum-in-Period-Where-Periods-Differ-By-Project/m-p/886536

     

     

    Column = CALCULATE(MAX(Position[Department]),FILTER(ALL(Position),Position[Start Date]>=Calendar[Dates] && Position[End Date]<=Calendar[Dates] && Calendar[NetID] = Position[NetID]))

     

     

    If this helps, mark it as a solution

    Kudos are nice too 

  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

    Hi Anonymous,

    Sorry for delay in response! You are right, you can't delete columns from a calculated table. (It does make sense but I didn't know that either untill you said so :P).

    You can circumvent this by using this calculated table instead (is uses SELECTCOLUMNS to select only the columns that you want):

    Calendar = 
VAR _tmpTable = GENERATE(Positions, 
    VAR _start = Positions[Start Date]
    VAR _end = Positions[End Date]
    RETURN
    GENERATESERIES(_start, _end))
RETURN
SELECTCOLUMNS(_tmpTable, "Date", [Value], "NetID", [NetID])

    Don't forget to like posts that helped you out (as a motivator :)) and to mark posts as solution if it provided the solution for you 🙂

    Good night!

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂

6 Replies

  • JarroVGIT's avatar
    JarroVGIT
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi Anonymous ,

    My advice would be to drop the Calendar table and create it dynamically from your Position data with the following DAX:

    Calendar = GENERATE(Positions, 
    VAR _start = Positions[Start Date]
    VAR _end = Positions[End Date]
    RETURN
    GENERATESERIES(_start, _end))

    That returns a row for every date between start date en end date for every row in the first table. It returns a couple more columns from the Positions table but you can delete those if you don't need those. Does this meet your requirements?

     

    Kind regards

    Djerro123

    -------------------------------

    If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

    Keep those thumbs up coming! 🙂

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      JarroVGIT 

      This does help me get what I need! How would I remove the other columns in this new table though? The delete option is greyed out when I select a column and the table is not showing up in power query.

      • JarroVGIT's avatar
        JarroVGIT
        Icon for Resident Rockstar rankResident Rockstar
        6 years ago

        Hi Anonymous,

        Sorry for delay in response! You are right, you can't delete columns from a calculated table. (It does make sense but I didn't know that either untill you said so :P).

        You can circumvent this by using this calculated table instead (is uses SELECTCOLUMNS to select only the columns that you want):

        Calendar = 
VAR _tmpTable = GENERATE(Positions, 
    VAR _start = Positions[Start Date]
    VAR _end = Positions[End Date]
    RETURN
    GENERATESERIES(_start, _end))
RETURN
SELECTCOLUMNS(_tmpTable, "Date", [Value], "NetID", [NetID])

        Don't forget to like posts that helped you out (as a motivator :)) and to mark posts as solution if it provided the solution for you 🙂

        Good night!

         

        Kind regards

        Djerro123

        -------------------------------

        If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

        Keep those thumbs up coming! 🙂

  • VasTg's avatar
    VasTg
    Icon for Memorable Member rankMemorable Member
    6 years ago

    Anonymous 

     

    I second the approach by JarroVGIT , but I would drop the fact and expand the table as he/she mentioned. Then define the relationship with Dates and the new fact.

     

    Refer to these posts.

     

    https://community.powerbi.com/t5/Desktop/Calculate-Sum-in-Period-Where-Periods-Differ-By-Project/m-p/886536

     

     

    Column = CALCULATE(MAX(Position[Department]),FILTER(ALL(Position),Position[Start Date]>=Calendar[Dates] && Position[End Date]<=Calendar[Dates] && Calendar[NetID] = Position[NetID]))

     

     

    If this helps, mark it as a solution

    Kudos are nice too 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      VasTg 

      This seems to have worked too after changing it to 

      "Position[Start Date]<=Calendar[Dates] && Position[End Date]>=Calendar[Dates]". I added it as a calculated column onto the Calendar table.
       
      What do you mean by "drop the fact"? And could you explain the logic behind using MAX here? Thank you!