Repeat a list of dates for a distinct range

[jamesmeek]

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.

[v-juanli-msft]

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]

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

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]

Thank you mussaenda

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

[Mariusz]

Hi @jamesmeek 

You can use Relative date filtering, like below

Best Regards,
Mariusz

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

Please feel free to connect with me.

[jamesmeek]

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:

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.

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.

[Mariusz]

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.

[mussaenda]

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]

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.