Return value if date is between 2 dates in another table

[Anonymous]

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

Dates	NetID
1/1/2020	a
1/1/2020	b
1/1/2020	c
1/2/2020	a
1/2/2020	b
1/2/2020	c
1/3/2020	a
1/3/2020	b
1/3/2020	c

Position

NetID	Start Date	End Date	Department
a	10/1/2019	12/1/2019	Dining
a	12/2/2019	3/1/2050	Res life
b	12/5/2019	3/1/2050	Housing
c	10/31/2018	11/5/2019	Housing
c	11/6/2019	3/1/2050	Dining

[VasTg · Accepted solution]

@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 

[btfergie]

Perfectly what I needed.

[VasTg · Accepted solution]

@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]

@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!

[JarroVGIT]

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]

@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 · Accepted solution]

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! 🙂