Carry over hours from previous months / balances

[aibrahim]

I was hoping to get this resolved here https://community.powerbi.com/t5/Desktop/Help-needed-Carry-over-balance-from-previous-month/m-p/2686... but unfortunately it's not fully resolved. Please help!

I'm having difficulty getting the below done and I really appreciate any help here. Here's what I'm trying to achieve: 

I have a row data that includes date, time spent (hours), monthly allowance budget. I want to achieve something like the screenshot, a table that has the following columns / structure:

• Month: this is basically the month in MM YY format
• Allowance of Hours: this is a fixed parameter which is a number of hours. We can say a monthly budget
• Hours Used: Hours used by users. This is coming from the row data
• Credit / Bonus: is a task type in the row file that's considered as bonus / credit which will be used in calculations
• Remaining Balance: Starting balance - Hours Used + Credit / Bonus
• Starting Balance: Allowance of hours + Remaining Balance from previous months. So it's a calculated column that takes the remaining hours from previous month and adds it to the monthly allowance. ONLY positive remaining hours will be carried over. Negative remaining will not be carried and in this case the starting balance will simply be the allowance of hours.
  
  So the condition should be something like: Starting balance = If remaining balance for previous month > 0 then Allowance of hours + Remaining Balance . Otherwise Starting balance = allowance of hours.
  
  
  
• Some additional requirements: Starting balance can be custom for the 1st month / hard coded, so we need to add a parameter and specify a date as an identifier for the month that has a custom starting balance. In this case (in screenshot), it's the month of March 2022 where it's showing 20.17 instead of 10.
  
  Attaching the file that has the excel calculations which has two tabs / worksheets. I'm also attaching the row file as a separate sheet. 

Download link: https://www.dropbox.com/s/h7lytu5z5pqjuey/BV.rar?dl=0

 

[grantsamborn]

Hi

Your requirements seem to be the same as in the original post which was marked as having a solution.

Can you let me know if this helps?

https://1drv.ms/u/s!AnF6rI36HAVkhPFIEXyYG5nrmQGzew?e=LwNISb

[aibrahim]

@grantsamborn any idea why the monthly allowance of August is missing although the new data file (loh) I uploaded had some entries in August so it should automatilcally be triggered. Any idea? But it looks good! 

[grantsamborn]

Hi

Re: Budget table - My budget table is hard-coded and was only going up to July.  I've since added the remaining months.  Alternatively, you could use this which was posted to your original topic.

monthly budget = 
VAR tab =
    DISTINCT (
        SELECTCOLUMNS (
            LOH__BV_report2,
            "Month", FORMAT ( [Date], "mmmm yyyy" ),
            "Rank", RANKX ( LOH__BV_report2, FORMAT ( [Date], "yyyymm" ),, ASC, DENSE )
        )
    )
RETURN
    ADDCOLUMNS ( tab, "Allowance of Hours", IF ( [Rank] = 1, 21.17, 10 ) )

Re: Data file - The most recent datafile that I have only has entries until July.

pbix: https://1drv.ms/u/s!AnF6rI36HAVkhPFJHbuFFqnEv8P61A?e=DiMqkj

[aibrahim]

I'm seeing this error when loading any source file. Any idea how to fix? I'd rather use your solution since the script above from the original post caused a few errors I guess.

[grantsamborn]

The previous datafiles had Project as a column.  If it no longer exists, you should upload a new datafile that reflects the change.

[aibrahim]

The datafile is exactly the same, hasn't changed. Although the file I'm uploading has Project column it's still giving me this error. 

I don't get this error on the older solution version you shared with me. 

[grantsamborn]

Does your datafile include 2 EXTRA header records and 1 footer record?  I have 3 versions of your datafile and that is the only difference.

In PowerQuery, 2 of my steps are to remove the header records and footer record.  If there isn't 2 extraneous header records, then the query will return the error you got because it would be deleting the expected header record (and the 1st record of data) and therefore not having the expected column names.

I hope this makes sense.

[aibrahim]

Hi @grantsamborn - that makes total sense. I was able to fix that. However, during testing I found errors; it looks like the starting balance isn't right. It's not calculting numbers right. Please see example below: 

So the below table was showing correct numbers up until July. July starting balance is wrong. As you can see in June we had 1.5h remaining balance (to be carried over to next month's starting balance). 

July's starting balance is 10 but it's supposed to be 11.5 (10+1.5). Same with September, it's showing 19.25 as starting balance but it's supposed to be 22.5 (10+12.5).

I'm attaching the files here: https://1drv.ms/u/s!AkK-ixi4x7Ixjg-CisdhhL-y7arm?e=XYZSUp

Please help! 

Thanks for working on this. 

[aibrahim]

@grantsamborn I'm highlighting where the logical error in the code, see below: 

You can't use allowance of hours here. It's wrong. 

[aibrahim]

@grantsamborn this has to be done through a counter variable. 

[grantsamborn]

I'm not sure what you mean by "counter variable".  Can you explain?

[aibrahim]

Hi @grantsamborn - I hope you feel better soon. In simple words, it's wrong to use "allowance of hours" as I highlighted in my previous screenshot. That should be replaced with "starting balance" - total hours + remaining hours. 

To explain more, here is the right equation in words: 

Starting Balance = Starting balance - total hours (this month) + remaining hours (from previous month If > 0). 

[grantsamborn]

OK - I understand now.

I'm not sure that can be done since the measure would be referencing itself.

The measures were written by @v-kkf-msft (Winniz) for your original thread.  Maybe you could try to contact Winniz.

[aibrahim]

I tried reaching out to @v-kkf-msft multiple times but lost communication. He doens't seem to be available anymore

[grantsamborn]

I'll take a look at it later on but I will be in the hospital for back surgery and am hoping to be home tomorrow.

[aibrahim]

@grantsamborn - I don't consider the original post resolved. It was almost resolved but I found bugs and issues as reported in my comments. 

Thanks for responding, I'll review the file you submitted.