Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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!

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

6 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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-Jul16-Jul17-Jul20-Jul21-Jul22-Jul 
      79.070.331.552.350.183.03 


      Expected View

      15-Jul16-Jul17-Jul20-Jul21-Jul22-Jul23-Jul
      1313131313138.51


      Let me know if you need more information.

      Sincerely!

      Naga2020

      • Fowmy's avatar
        Fowmy
        Super User
        6 years ago

        Anonymous 

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