topic Re: Countdown of SUM Total for week forecast in DAX Commands and Tips

Countdown of SUM Total for week forecast

Anonymous — Mon, 16 Mar 2020 17:04:30 GMT

Hello

We have a table that shows a total of items dispatched through out the week as shown in Table 1. I have been asked to produce a Table (as shown in Table 2) which essential counts down through the week to show how much could be left to go out.

Table 1

		location a	location b	location c
1	Mon	118	59	278
2	Tue	173	88	343
3	Wed	182	71	337
4	Thu	173	66	301
5	Fri	195	72	335
6	Sat	41	23	109
7	Sun	4	5	17

Table 2

		location a	location b	location c
1	Mon	886	384	1720
2	Tue	768	325	1442
3	Wed	595	237	1099
4	Thu	413	166	762
5	Fri	240	100	461
6	Sat	45	28	126
7	Sun	4	5	17

Essentially, Table 2 does a SUM of all values in the relevant column for days equal to or greater than. For example, for Table 2 on Wednesday it will only look at Table 1 Wednesday to Sunday and for Friday it will only look at Table 1 Friday to Sunday in the relevant locations.

Any guidance would be massively appreciated! 🙂

Re: Countdown of SUM Total for week forecast

Greg_Deckler — Mon, 16 Mar 2020 17:35:43 GMT

Maybe Days of Supply?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

Re: Countdown of SUM Total for week forecast

Anonymous — Tue, 17 Mar 2020 09:19:49 GMT

Hello Greg_Deckler

I don't believe this would work for me as our data is in individual lines so it appears as:

ID	Location	DateTime	Day	Comments
1	a	10/03/2020 14:58	Tue	collected late due to traffic
2	b	10/03/2020 15:55	Tue
3	a	11/03/2020 10:01	Wed	issue with collection system
4	c	11/03/2020 13:44	Wed
5	a	14/03/2020 09:01	Sat

So I need it to essentially COUNT all rows, and depending what day of the week it is, it will need to could all days or from Wednesday onwards for example.

I have a seperate table for days with an ID so I can sort it in the order i require:

ID	Day
1	Mon
2	Tue
3	Wed
4	Thu
5	Fri
6	Sat
7	Sun

Re: Countdown of SUM Total for week forecast

Greg_Deckler — Tue, 17 Mar 2020 12:36:29 GMT

Wait, that table doesn't look like the tables you posted earlier, what is the correct representation of your source data?

Re: Countdown of SUM Total for week forecast

Anonymous — Tue, 17 Mar 2020 13:21:43 GMT

Hello Greg_Deckler

Apologies, the table I just sent is the raw data before I do anything to it. It has a line for each item delivered with date/time, location code and day.

From my original post, Table 1 is what I have in PBI using a Matrix which shows the totals for each day i.e. Mon-Sun and Location and Table 2 is the desired outcome for what I need. This is essentially a SUM of all Table 1 Mon-Sun then on Tuesday Tue-Sun etc etc. The forumla I would expect to be a COUNT of the raw data and determines if Mon then count all Mon-Sun whereas if it is Wed it would count all Wed-Sun.

We have already built this in Excel but this uses Table 1 data which I dont believe we can do in PBI?

Re: Countdown of SUM Total for week forecast

Anonymous — Tue, 17 Mar 2020 14:21:03 GMT

Hi there.

"We have already built this in Excel but this uses Table 1 data which I dont believe we can do in PBI?"

You can do ANYTHING in Power BI. But for data transformation you need to use Power Query, not DAX.

Best
D

Re: Countdown of SUM Total for week forecast

Anonymous — Tue, 17 Mar 2020 14:34:10 GMT

Hello Anonymous

Love the enthusiasm! 😀

Just with the calculation, I have added WEEKDAY(Date,2) so that for the Date/Time I have a column next to it to say 1 for Mon up to 7 for Sun. Could I then use the table which has the ID and Day to do something like the below (but in a format that works! 😅)

CALCULATE(COUNT(Delivery[DateTime])+0,FILTER(Delivery,Delivery[Weekday]>=Days[ID]))

The idea is that the count will add all the data which I will split using the Matrix as shown on Table 2 then using the calculate/filter to count where weekday is greather than or equal to the day. If that makes sense!

Re: Countdown of SUM Total for week forecast

Anonymous — Tue, 17 Mar 2020 14:42:03 GMT

I can see your raw data above.... but I don't think it's all there is to it. You only say about days of the week but I suspect this is not the full story. If you have dates in your rows, they can come from different years. When you drop just plain names of days onto a table visual, you could then have days from different months and years aggregated. This is certainly not what you want.

So, the real question is: What in fact is your data and how the measure should behave when not only days are selected but months, quarters...?

Can you please enlarge upon this a bit?

Best
D

Re: Countdown of SUM Total for week forecast

Anonymous — Tue, 17 Mar 2020 14:45:55 GMT

Hello Anonymous

Within PBI I have limited the data to only pull back the last 4 weeks so for us this will suit our estimated forecast based on previous weeks figures.

Essentially, we will be using the data to say, for the rest of the week (Tue-Sun) we are expecting 589 deliveries to take place from Location A. This is why we only need to look at the full 4 weeks data (divided by 4 to create an average) for our forecasts.

Re: Countdown of SUM Total for week forecast

Anonymous — Tue, 17 Mar 2020 18:47:52 GMT

Hi there... I'm not entirely clear about what you really want. Please have a look at the file and decide if you can use it. Maybe you just have to tweak it a bit to make it work. No idea.

https://1drv.ms/u/s!ApyQEauTSLtOgYMxIzn3upDklkgToQ?e=QKbGiC

Best