Forum Discussion
Return value if date is between 2 dates in another table
Resident RockstarHi 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! 🙂
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
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! 🙂