# Power BI Exchange

View Only

## Use previous month to calculate a value in next month

• #### 1.  Use previous month to calculate a value in next month

Silver Contributor
Posted Aug 12, 2020 02:55 PM
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-

• #### 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)
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!
 Date Name Adj Prod Target Adj-Target Previous Deficit Carried Forward deficit Paid Current Remarks 4/1/2019 Barbara -5.5 0 May 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/2019 Barbara 104.25 100 4.25 -5.5 -1.25 0 6/1/2019 Barbara 115.25 110 5.25 -1.25 0 4 7/1/2019 Barbara 87.5 110 -22.5 0 -22.5 0 8/1/2019 Barbara 0 110 0 -22.5 -22.5 0 9/1/2019 Barbara 0 110 0 -22.5 -22.5 0 10/1/2019 Barbara 90 100 -10 -22.5 -32.5 0 Carried Forward Deficit = (adj-Target)+(Previous Deficit) 11/1/2019 Barbara 92 100 -8 -32.5 -40 0 -80 12/1/2019 Barbara 120 100 20 -40 -20 0 1/1/2020 Barbara 115 100 5 -20 -15 0 2/1/2020 Barbara 103 110 -7 -15 -22 0 3/1/2020 Barbara 102 110 2 -22 -20 0 4/1/2020 Barbara 130 100 30 -5 0 25 Changed 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
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
------------------------------

Attachment(s)

• #### 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
------------------------------