cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jamesmeek Frequent Visitor
Frequent Visitor

Repeat a list of dates for a distinct range

Good morning,

 

I'm struggling with a report to forward plan machine capacity.

 

I have several machines and wish to repeat a 2 week calander for each machine all in one table eg

 

14/08/19     Machine 1

15/08/19     Machine 1

16/08/19     Machine 1

19/08/19     Machine 1

20/08/19     Machine 1

21/08/19     Machine 1

22/08/19     Machine 1

14/08/19     Machine 2

15/08/19     Machine 2

16/08/19     Machine 2

19/08/19     Machine 2

20/08/19     Machine 2

21/08/19     Machine 2

22/08/19     Machine 2

14/08/19     Machine 3

15/08/19     Machine 3

16/08/19     Machine 3

19/08/19     Machine 3

20/08/19     Machine 3

21/08/19     Machine 3

22/08/19     Machine 3

 

I can handle non-working days like weekends and holidays, but can't work out how to have the dates be dynamic to show a select number of dates forward from today.

 

Can you help?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
mussaenda Senior Member
Senior Member

Re: Repeat a list of dates for a distinct range

From my understanding, you want a full range of dates per machine, on a table. Right?

 

Create a Date table then on power query, go to you table where your machines are, add custom column,

= Name of Date Table.

 

you can expand the added column and the date range will be on each machine.

9 REPLIES 9
Super User
Super User

Re: Repeat a list of dates for a distinct range

Hi @jamesmeek 

 

You can use Relative date filtering, like below

 

image.png

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

jamesmeek Frequent Visitor
Frequent Visitor

Re: Repeat a list of dates for a distinct range

Hi @Mariusz 

Thanks for your reponse, I should have been clear I'm trying to build a data table rather than a visual at the moment.

 

I've got this far:

Capture.JPG

 

I'm pulling the data from our systems, showing the cummulative outstanding area to process for this machine. It has a capacity of 25m2 per day.

 

Capture 2.JPG

At present my date is taken from the system, and as you can see I don't have a 14th of August as nothing is currently planned on that date for that machine.

 

I need to have a date range that shows all available dates as we should have an unused capacity of 25m2 on the 14th, but it is not showing in the way that I have created the date range at pressent. 

 

I hope that makes sense.

 

mussaenda Senior Member
Senior Member

Re: Repeat a list of dates for a distinct range

You must have a Date Table to have a complete date range.

here is the link of the date table code: Date Table

 

then you can use this  table to meet your requirements

jamesmeek Frequent Visitor
Frequent Visitor

Re: Repeat a list of dates for a distinct range

Hi @mussaenda,

 

I have tried this approach, but I have more than one process to assign to each date and all DAX formulae that I have tried have errored.

Super User
Super User

Re: Repeat a list of dates for a distinct range

Hi @jamesmeek 

 

You need to create Date Dimension / Calendar table and join on dates, this will give you ability to display the missing dates.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski

 

Community Support Team
Community Support Team

Re: Repeat a list of dates for a distinct range

Hi @jamesmeek 

What is your final requirement?

Is it as below ?

select "2" from a slicer, then a table visual shows 2 weeks forward from today.

the dates repeat for each machine.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jamesmeek Frequent Visitor
Frequent Visitor

Re: Repeat a list of dates for a distinct range

Hi @v-juanli-msft,

 

my required output is a data table (not a visual) that I can pull more details into. I have ~10 machines in a table with the date that work is assigned to them and the qty of work that has been assigned already.

 

I want to make a table that has every day of the month for every machine code. Something like this:

 

14/08/19     Machine 1

15/08/19     Machine 1

16/08/19     Machine 1

19/08/19     Machine 1

20/08/19     Machine 1

21/08/19     Machine 1

22/08/19     Machine 1

14/08/19     Machine 2

15/08/19     Machine 2

16/08/19     Machine 2

19/08/19     Machine 2

20/08/19     Machine 2

21/08/19     Machine 2

22/08/19     Machine 2

14/08/19     Machine 3

15/08/19     Machine 3

16/08/19     Machine 3

19/08/19     Machine 3

20/08/19     Machine 3

21/08/19     Machine 3

22/08/19     Machine 3

 

 

I can then use dax to pull into this table the capacity of the machine for that day and the qty of work currently planed to show spare capacity/over use.

 

 

mussaenda Senior Member
Senior Member

Re: Repeat a list of dates for a distinct range

From my understanding, you want a full range of dates per machine, on a table. Right?

 

Create a Date table then on power query, go to you table where your machines are, add custom column,

= Name of Date Table.

 

you can expand the added column and the date range will be on each machine.

jamesmeek Frequent Visitor
Frequent Visitor

Re: Repeat a list of dates for a distinct range

Thank you mussaenda

 

I was trying to do it all as DAX, I hadn't thought of doing it in the Power Query Editor.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 42 members 933 guests
Please welcome our newest community members: