Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Use previous month to calculate a value in next month

    Silver Contributor
    Posted Aug 12, 2020 02:55 PM
      |   view attached
    Hello Power BI Gurus from a mere mortal. 
     My problem is  I need to calculate hours to be paid for current month based on other values from current month minus a deficit carryover from the previous month.  

    The formula for each person-month  is  Paid Hours =Target Hours- Adjusted Hours - Most Recent Previous Month Deficit Hours.  Previous Month Deficit Hours are  equal to any negative values for Paid Hours from  the most recent previous month.  (A bit recursive like inventory?)

    I can create a table with name, month, name-month, Target Hours and Adjusted Hours.for May-July. I have initial set of Paid Hours from May (i.e. Previous Deficit Hours) that can be used  in a measure or calculated column for the June Paid Hours (or a subsequent Month if there are no adjusted hours reported for June).  The problem is how to use the may deficit for the next worked month only, then use the most recent previous month Paid Hours as the carry forward deficit  for the subsequent month calculations.  Please see attached. Note, some names that were not captured in the screenshot have may carry overs.  
    Thanks, Len- 
    image.png


  • 2.  RE: Use previous month to calculate a value in next month

    Top Contributor
    Posted Aug 13, 2020 07:54 AM
    This approach might be helpful in your scenario

    https://community.powerbi.com/t5/DAX-Commands-and-Tips/Rolling-12-Month-Average-result-used-to-Forecast/m-p/1222798#M19865

    ------------------------------
    Lutz
    ------------------------------



  • 3.  RE: Use previous month to calculate a value in next month

    Top Contributor
    Posted Aug 14, 2020 03:26 PM
    Hi @Leonard Levine

    Have you got your solution from the link @Lutz Bendlin suggested? If not, is it possible for you to give sample data in excel? ​​If I undersand you correctly, for calculation of 'Paid hours', you need to consider the most recent deficit hours. And once that is used up, it should not be considered for any future calculations. Is there a situation where the deficit is partly adjusted? If so, what happens to the part not adjusted? What happens if two months consecutively has defecits? Only the latest month is carried forward rt? And one more thing..the pic shows may deficit as a column, does this mean that every month you have a new column for the deficit?

    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------



  • 4.  RE: Use previous month to calculate a value in next month

    Silver Contributor
    Posted Aug 15, 2020 10:42 AM
    Thank you both.  Unfortunately I am still unable to create the solution but will attempt to answer Gopa's questions:
    ​​If I undersand you correctly, for calculation of 'Paid hours', you need to consider the most recent deficit hours (YES, BUT ONLY IF THEY ARE NEGATIVE, WE DO NOT PAY UNLESS THEY ARE USED UP)  . And once that is used up, it should not be considered for any future calculations (NO, IT BECOMES ZERO FOR THE NEXT MONTH); IF THE NEXT MONTH RESULT IS NEGATIVE, IT IS CARRIED FORWARD AGAIN UNTIL USED UP). Is there a situation where the deficit is partly adjusted? (KIND OF, THE DEFICIT IS ALWAYS ADJUSTED FOR THE NEXT MONTH BASED ON THE PREVIOUS MONTH RESULT ) If so, what happens to the part not adjusted?(IF THE PERSON DID NOT WORK THE FOLLOWING MONTH BUT CAME BACK IN TWO MONTHS THE EXISTING PREVIOUS DEFICIT WOULD BE USED) What happens if two months consecutively has defecits?  (THIS WILL HAPPEN ALOT SINCE THE DEFICIT IS CARRIED FORWARD MONTH TO MONTH AND CHANGED BY THE PREVIOUS MONTH CALCULATION) Only the latest month is carried forward rt? *(YES IT ROLLS) And one more thing..the pic shows may deficit as a column, does this mean that every month you have a new column for the deficit? ( NO, BUT COULD BE POSSIBLY,,  I LABELED THAT COLUMN BECAUSE I HAD THAT INITIAL PAID HOURS FOR MAY AND COULD THEN USE IT AS THE CARRYOVER DEFICIT FOR JUNE (ADJ-JUNE)-(TARGET-JUNE)- (PAID -MAY),  Note Paid May and Deficit to be used for MAY are the same. PAID for Subsequent Month becomes Deficit for the following.  The May Paid or deficit Hours are like a seed to get the calculations going moving forward but after that they need to be calculated based on their use in the formula for the previous month.  Also, If deficits are positive those hours are literally paid to employee setting the deficit to zero for use in the next month.Below is another Visual that may help. The -24.15 May deficit was provided by user.  The 7/1 value is O but should be -23.9 and that is my challenge.  Again Thanks!!!


    ------------------------------
    Leonard Levine
    02048
    Mansfield MA
    339 364 2798
    ------------------------------



  • 5.  RE: Use previous month to calculate a value in next month

    Silver Contributor
    Posted Aug 15, 2020 10:47 AM
    HERE IS THE PIC


    ------------------------------
    Leonard Levine
    02048
    Mansfield MA
    339 364 2798
    ------------------------------



  • 6.  RE: Use previous month to calculate a value in next month

    Top Contributor
    Posted Aug 16, 2020 07:01 AM
    Hi @Leonard Levine

    Thanks for the info. I apologize that the business logic is still not clear for me nor is your data model. Below is a working which I have made based on my understanding. I have tried to incorporate the different scenarios which I could think off. Is this understanding correct? If not, is it possible for you to represent your business logic in a similar fashion?



    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------



  • 7.  RE: Use previous month to calculate a value in next month

    Silver Contributor
    Posted Aug 17, 2020 06:40 AM

    Len Levine <lenlevine1@gmail.com>

    Aug 16, 2020, 10:26 AM (20 hours ago)
    to DoNotReply
    Thanks so much Gopa.  I continue to appreciate your time and help.  Below are the few adjustments to the model you sent. I hope they make sense based on the fact that negative deficits accumulate.  Two other minor points.  We only receive the May deficit not the adj or target but that is ok since we only need to report Paid for June and subsequent months after that. Last, adj, target and target-adj are actually calculated columns in my table, not sure if that matters.  The data provided by users has had to be transformed (days to months, totals over sick, vacation and holiday etc in initial formulas),,, I do not think this will matter to your solution but wanted you to know just in case..  Thanks again!
    DateNameAdj ProdTargetAdj-TargetPrevious DeficitCarried Forward deficitPaid CurrentRemarks
    4/1/2019Barbara    -5.50May Deficits (-5.5)are provided in this example by the user. The carried forward and paid for May are not provided and not needed as the model would only need to process June and subsequent months paid values.  
    5/1/2019Barbara104.251004.25-5.5-1.250 
    6/1/2019Barbara115.251105.25-1.2504 
    7/1/2019Barbara87.5110-22.50-22.50 
    8/1/2019Barbara01100-22.5-22.50 
    9/1/2019Barbara01100-22.5-22.50 
    10/1/2019Barbara90100-10-22.5-32.50Carried Forward Deficit = (adj-Target)+(Previous Deficit)
    11/1/2019Barbara92100-8-32.5-400-80
    12/1/2019Barbara12010020-40-200 
    1/1/2020Barbara1151005-20-150 
    2/1/2020Barbara103110-7-15-220 
    3/1/2020Barbara1021102-22-200 
    4/1/2020Barbara13010030-5025Changed this adj and target to show that you are correct that positive values are paid 






  • 8.  RE: Use previous month to calculate a value in next month

    Top Contributor
    Posted Aug 18, 2020 08:45 AM
      |   view attached
    Hi @Leonard Levine

    Thanks for your response. Please see if the following helps you. I have done this in power query using a function. If this is something which resolves your issue, please check the performance against your data.


    Function in Power Query

    (employee as text, rlvntdate as date) => 
      let
        Source = Table.Sort(
            Table.SelectRows(Productivity1, each [Staff] = employee and [Date] < rlvntdate), 
            {"Date"}
          ), //filter to the relevant staff and for period less than the current date. Also sort the filtered table datewise ascending
        adjhrslist = Source[AdjTar Hrs], //makea the adj hrs to a list
        TotalLoops = List.Count(adjhrslist), //get list count to stop the looping
        currentsum = 0, //variable initiation
        //---------------------------------------------------------------------------------
        fnloop = (loop as number, opsum as number) =>  //inside function to loop against the filtered table
          let
            currentloop = loop + 1, //loop counter
            currentlistindex = currentloop - 1, //index of the list starts from 0 hence we reduce 1 from currentloop which is starting from 1
            currentvalue = adjhrslist{currentlistindex}, //gets the adjhrs in the current loop context
            currentsum = opsum + currentvalue, //add the current list value to the previous loop sum
            cfSum = if currentsum > 0 then 0 else currentsum, //reset the sum to 0 if the current sum > 0
            output = if currentloop >= TotalLoops then cfSum else @fnloop(currentloop, cfSum) //loop until it reaches the end of the list
          in
            output,
        //---------------------------------------------------------------------------------
        finaloutput = try fnloop(0, 0) otherwise 0 //we are using try because the inital row wil throw an error bcoz of no previous value. so we are replacing the error with 0
      in
        finaloutput

    PFA the pbix file for your reference.


    ------------------------------
    Gopa Kumar
    Limner Consulting

    North Kerala User Group Leader
    https://www.pbiusergroup.com/NorthKerala
    ------------------------------



  • 9.  RE: Use previous month to calculate a value in next month

    Posted 22 days ago

    Hello @Gopa Kumar Sivadasan,
    I have similiar problem to solve. 
    I calculated Project EOM Stock using rolling method,  however, I need it return to 0 and restart again when it negative. 
    My wish outcome as shown: 

    ​Thank you for advise :) 



    ------------------------------
    betty bui
    ------------------------------