Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nkasdali
Employee
Employee

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 :

A.JPG

 

And this what i'm looking to build :

b.JPG

 

Thanks for your help.

 

2 ACCEPTED SOLUTIONS
ChandeepChhabra
Impactful Individual
Impactful Individual

@nkasdali

 

Any particular reason why do you want to do this with DAX? You can also use powerquery to achieve the same result

 

Capture3.PNG

 

Take a look at this pbix

 

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

In the Query Editor window, create this custom column

 

={Number.From([Start])..Number.From([End])}

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ChandeepChhabra
Impactful Individual
Impactful Individual

@nkasdali

 

Any particular reason why do you want to do this with DAX? You can also use powerquery to achieve the same result

 

Capture3.PNG

 

Take a look at this pbix

 

Hooo, @ChandeepChhabra it looks like cool :

 

Can you please send me the Book1.xls, to understand the steps.

 

🙂

Greg_Deckler
Super User
Super User

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.