Forum Discussion
Using Start & End date from a table and build another table with continous dates
Hi All,
If you're a DAX expert! I need your help, please :)
So, I have a table with START & End Date, using this fields i need to build another table with a continuous date between the precedent fields.
As i said, this is my input :
And this what i'm looking to build :
Thanks for your help.
Any particular reason why do you want to do this with DAX? You can also use powerquery to achieve the same result
8 Replies
Check this out:
AgentTable = VAR tmpCalendar = CALENDAR(MIN(Agents[Start]),MAX(Agents[End])) VAR tmpAgent1 = FILTER(Agents,[ID]=1) VAR tmpAgent2 = FILTER(Agents,[ID]=2) VAR tmpTable1 = SELECTCOLUMNS( FILTER( GENERATE(tmpAgent1,tmpCalendar), [Date]>=[Start] && [Date]<=[End] ), "ID",[ID],"Agent",[Agent],"Date",[Date] ) VAR tmpTable2 = SELECTCOLUMNS( FILTER( GENERATE(tmpAgent2,tmpCalendar), [Date]>=[Start] && [Date]<=[End] ), "ID",[ID],"Agent",[Agent],"Date",[Date] ) RETURN UNION(tmpTable1,tmpTable2)Thanks greg, but i don't know how much agent i'll have :(
Well, then you have a problem because of your varying start and end dates. :(
I might have time to take a look at this later or perhaps Phil_Seamark or someone could assist with this as well.
Might also have to rework your data a little to unpivot your start and end date columns, that might be a path towards a solution as well.
I'm just not coming up with anything off the top of my head because you essentially need a separate generated table for each agent that you then need to append all together.
Why don't you know how many agents you have? It isn't in the data? Or is that more of a general statement that you have too many agents for this solution to be viable?
Any particular reason why do you want to do this with DAX? You can also use powerquery to achieve the same result
Hooo, ChandeepChhabra it looks like cool :
Can you please send me the Book1.xls, to understand the steps.
:)
Use GENERATE along with a CALENDAR table. So, create a CALENDAR table that is essentially:
Calendar = CALENDAR(MIN([Start]),MAX([End]))
Then use GENERATE. Might have to do some filtering and such, I have some recently posted examples of using GENERATE in the Quick Measures gallery. Otherwise, Phil_Seamark has the best information on the use of GENERATE that I have seen in his great new book, https://www.amazon.com/Beginning-DAX-Power-BI-Intelligence/dp/1484234766
Hi,
In the Query Editor window, create this custom column
={Number.From([Start])..Number.From([End])}
Hope this helps.
