Distribute total value to dates using total capacity until dates are filled

[Anonymous]

Hello, I am new to Power BI. I am using 3 sources to combine all estimated and total hours  by work centre. I managed to combine hours using a calendar for dates. Next step was to remove exported nesting records from the 3rd source that are already reported from the first source. So far so good. Let's call it sources A, B and C. In one graph, All hours are moved to the current date that are already past due. The first graph is using sources A and C. The problem is, the current date will have a sum that exceeds the capacity for this work centre. Example is today's total is 40 and the capacity is 12. Other dates can have values too. I used source C to sum each sources with the use of SWITCH by work center. Like atotal, btotal and ctotal. (Example: IF(atotal > 0, atotal, ctotal).  Now, I have to distribute the total hours by capacity to each dates so that dates will be filled until all the total hours are used. I tried different approaches but so far I have not succeeded. Is this possible in Power BI?

Any ideas or help will be greatly appreciated.

Thanks in advance!

[Fowmy]

@Anonymous 

Please share some source data along with a short question and an explanation, if you could show the expected result as well.

Thanks

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS ? to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

[Anonymous]

Hi Fowmy,

Thank you for looking into my Power BI problem. I have created a Power BI report using excel files to mimic the issue I am having. I am using Power BI Desktop 32 bit.
Here is the pbix file link: https://www.dropbox.com/s/4kibmfmr5h5jbtp/DistributeHoursToDates.pbix?dl=0

Current View

15-Jul	16-Jul	17-Jul	20-Jul	21-Jul	22-Jul
79.07	0.33	1.55	2.35	0.18	3.03

Expected View

15-Jul	16-Jul	17-Jul	20-Jul	21-Jul	22-Jul	23-Jul
13	13	13	13	13	13	8.51

Let me know if you need more information.

Sincerely!

Naga2020

[Fowmy]

@Anonymous 

Can you explain how for example 13 on Jul 16 is calculated?

[Anonymous]

@Fowmy

13 is the Total Capacity per day for the example work centre. In the example graph the exact capacity is 12.72 from the Main.xlsx file. The resource Excel files can be downloaded here: https://www.dropbox.com/s/ecx2kf2ay40fu4r/Main.xlsx?dl=0 and here https://www.dropbox.com/s/51fsebf1qy73bu8/SUB.xlsx?dl=0. 79.07/13 = 6.0769. I distributed 13 to 6 days. 79.07 is the Total Time for July 15 where all Due Dates are always moved to the current day. Since we move all past due dates to the current day, the 79.07 will be now added to today's total time. Today being July 16. Hope that makes sense. Let me know if you need more information. Thanks!

[Anonymous]

Hello

I have tried to manipulate the data in the access and create a query by using the operators and MOD. So I have this result.

I used 16 as the ability to get the number of days and the remaining time to lose.

This is the query in the access:

I was able to join on access to distribute the dates by using a calendar table. Unfortunately, Power BI does not recognize a union of queries. I've been checking DAX functions to create a table to do the join, but it seems like I have to do another query to get the acceptable join in PBI. Please help if the forecast below is feasible in PBI.

Thanks in advance for any ideas or solutions to get this started.

[Anonymous · Accepted solution]

If there is a better way please feel free to suggest any ideas.
I have managed to spread out the capacity from the first day and include the affected days that the distribution will use in the report.
First I have to exclude the work orders from source B that is already used in source A by using FILTER and CALCULATETABLE

Example: Create a table

LaserB=FILTER(CALCULATETABLE(GSS,EXCEPT(VALUES(GSS[JOB]),VALUES(PBIWOrder[WOrder]))),GSS[WorkCenter]="LA3")

then combine the 2 sources using UNION and SELECTCOLUMNS.

Lasers=

UNION(

SELECTCOLUMNS(LaserB,"DayDue",GSSLA3[DayDue], "TotalTime", GSSLA3[EST_HRS], "WC", GSSLA3[WorkCenter], "OrigDate", GSSLA3[DATE]),

SELECTCOLUMNS(PBI,"DayDue", PBI[DayDue2],"TotalTime",PBI[TotalTime],"WC",PBI[WC], "OrigDate", PBI[DayDue])

)

then here's the fun part:

I have to use several derived tables to manipulate the data by using SUMMARIZE:

LasersCurrentTotalTime =

var currentDate = TODAY()

var cap = 24

return

SUMMARIZE(FILTER(Lasers,Lasers[DayDue]=currentDate), Lasers[DayDue], Lasers[WC],

"TotalTime", SUM(LA[TotalTime]),

"Days", QUOTIENT(SUM(Lasers[TotalTime]),cap),

"REM", MOD(SUM(Lasers[TotalTime]),cap))

I also have to consider the weekday as the data only includes weekdays. So after getting the summary, I had to get the affected days too.

LasersAffectedDaysTotalTime =

var currentDate = TODAY()

var days = MAX(LasersCurrentTotalTime[Days])

return

SELECTCOLUMNS(

FILTER(Lasers,Lasers[DayDue] > currentDate

&& Lasers[DayDue] <= currentDate

+ days + IF(days > 0,1,0)

+ IF(WEEKDAY(Lasers[DayDue] + days + IF(days > 0,1,0)) = 1, 1, 0)

+ IF(WEEKDAY(Lasers[DayDue] + days + IF(days > 0,1,0)) = 7, 2, 0)

After getting the affected dates, I had to get the new current day records.

LasersCurrentDayTotalTime =

var currentDate = TODAY()

return

SELECTCOLUMNS(

FILTER(Lasers,Lasers[DayDue]=currentDate),

"DayDue", Lasers[DayDue], "Total", Lasers[TotalTime], "WC", Lasers[WC], "OrigDate", Lasers[OrigDate]

)

),

"DayDue", TODAY(), "Total", Lasers[TotalTime], "WC", Lasers[WC], "OrigDate", Lasers[OrigDate])

Combine the current and affected days.

LasersCurrentAndAffectedDaysTotalTime =

UNION(

SELECTCOLUMNS(LasersCurrentDayTotalTime,

"DayDue", TODAY(), "Total", LasersCurrentDayTotalTime[Total], "WC", LasersCurrentDayTotalTime[WC], "OrigDate", LasersCurrentDayTotalTime[OrigDate]),

SELECTCOLUMNS(LasersAffectedDaysTotalTime,

"DayDue", TODAY(), "Total", LasersAffectedDaysTotalTime[Total], "WC", LasersAffectedDaysTotalTime[WC], "OrigDate", LasersAffectedDaysTotalTime[OrigDate])

)

And summarize again as this will be the new summary that includes the current and affected dates of the distribution.

LasersDistributionSummary =

var currentDate = TODAY()

var cap = 24

return

SUMMARIZE(LasersCurrentAndAffectedDaysTotalTime,

"DayDue", currentDate,

"TotalTime", SUM(LasersCurrentAndAffectedDaysTotalTime[Total]),

"Days", QUOTIENT(SUM(LasersCurrentAndAffectedDaysTotalTime[Total]),cap),

"REM", MOD(SUM(LasersCurrentAndAffectedDaysTotalTime[Total]),cap),

"WC", MAX(LasersCurrentAndAffectedDaysTotalTime[WC]),

"LastDay", MAX(LasersCurrentAndAffectedDaysTotalTime[OrigDate])

)

 

We're getting there now. Get the distributed capacity while minding the days and exclude weekends.

LasersDistributedCapacity =

var currentDate = TODAY()

var weekDayCurrent = WEEKDAY(currentDate)

var days = MAX(LasersDistributionSummary[Days]) - 1

var cap = 24

return

SELECTCOLUMNS(

FILTER('Calendar', 'Calendar'[Date] >= currentDate && 'Calendar'[Date] <= currentDate + days),

"DayDue", //'Calendar'[Date]

SWITCH('Calendar'[Date],

currentDate, 'Calendar'[Date] +

SWITCH(weekDayCurrent,

6,0, 5,0, 4,0, 3,0, 2,0, 1,1, 7,2),

currentDate + 1, 'Calendar'[Date] +

SWITCH(weekDayCurrent,

6,2, //Friday

5,0, //Thursday

4,0, //Wednesday

3,0, //Tuesday

2,0, //Monday

1,1, //Sunday

7,2), //Saturday

currentDate + 2, 'Calendar'[Date] +

SWITCH(weekDayCurrent, 6,2, 5,2, 4,0, 3,0, 2,0, 1,1, 7,2, 0),

currentDate + 3, 'Calendar'[Date] +

SWITCH(weekDayCurrent,

6,2, 5,2, 4,2, 3,0, 2,0, 1,1, 7,2, 0),

currentDate + 4, 'Calendar'[Date] +

SWITCH(weekDayCurrent, 6,2, 5,2, 4,2, 3,2, 2,0, 1,1, 7,2, 0),

currentDate + 5, 'Calendar'[Date] +

SWITCH(weekDayCurrent, 6,2, 5,2, 4,2, 3,2, 2,2, 1,3, 7,4, 0),

'Calendar'[Date]),

"Total", cap,

"DateOrig", 'Calendar'[Date],

"WeekDayOrig", WEEKDAY('Calendar'[Date]),

"LastDay", WEEKDAY( currentDate + days),

"Rank",RANKX(FILTER('Calendar', 'Calendar'[Date] >= currentDate && 'Calendar'[Date] <= currentDate + days),'Calendar'[Date],'Calendar'[Date],ASC),

"WC", MAX(LasersDistributionSummary[WC]))

And here's the last table that is used in the graph.

LasersDistributedCapacityFull =

var currentDate = TODAY()

var days = MAX(LasersCurrentTotalTime[Days])

var lastDistributedDate = MAX(LasersDistributedCapacity[DayDue])

var remDate = IF(lastDistributedDate = BLANK(), currentDate, lastDistributedDate + IF(days > 0,1,0))

var lastAffectedDate = MAX(LasersCurrentAndAffectedDaysTotalTime[OrigDate])

var remWeekDay = WEEKDAY(remDate)

var remTotal = MAX(LasersDistributionSummary[REM])

var LasersDate = currentDate + days

return

UNION(

///*

//Distributed current and affected total time

SELECTCOLUMNS(LasersDistributedCapacity,

"DayDue",LasersDistributedCapacity[DayDue],

"Total",LasersDistributedCapacity[Total],

"DateOrig",LasersDistributedCapacity[DateOrig],

"WeekDayOrig",LasersDistributedCapacity[WeekDayOrig],

"LastDay",LasersDistributedCapacity[LastDay],

"Rank",RANKX(FILTER('LasersDistributedCapacity', LasersDistributedCapacity[DayDue]),LasersDistributedCapacity[DayDue],LasersDistributedCapacity[DayDue],ASC),

"WC",LasersDistributedCapacity[WC]),

//*/

///*

//Get the remaining total time from Lasers table

SELECTCOLUMNS(FILTER('Calendar', 'Calendar'[Date] = remDate),

"DayDue",

SWITCH('Calendar'[Date],

remDate, 'Calendar'[Date] +

SWITCH(remWeekDay,

1,1,

7,2,

0)),

"Total", remTotal,

"DateOrig", 'Calendar'[Date],

"WeekDayOrig", WEEKDAY('Calendar'[Date]),

"LastDay", WEEKDAY( remDate),

"Rank",RANKX(FILTER('Calendar', 'Calendar'[Date] = remDate),'Calendar'[Date],'Calendar'[Date],ASC),

"WC", MAX(LasersDistributionSummary[WC])),

//*/

//Get the remaining total time from LA3 table

///*

SELECTCOLUMNS(FILTER(Lasers,Lasers[DayDue] > lastAffectedDate),

"DayDue", Lasers[DayDue],

"Total", Lasers[TotalTime],

"DateOrig", Lasers[OrigDate],

"WeekDayOrig", WEEKDAY(Lasers[OrigDate]),

"LastDay", WEEKDAY(TODAY()),

"Rank",RANKX(FILTER('Lasers', 'Lasers'[DayDue] > currentDate + days),'Lasers'[DayDue],Lasers[DayDue],ASC),

"WC", Lasers[WC])

// */

)

You will find that there are a lot of comments here because I was testing the result. Feel free to suggest a better way that will not eat resources. I am using a Calendar in the report for the date.

Thanks!