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:
Download link: https://www.dropbox.com/s/h7lytu5z5pqjuey/BV.rar?dl=0
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?
@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!
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.
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.
The previous datafiles had Project as a column. If it no longer exists, you should upload a new datafile that reflects the change.
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.
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.
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
Thanks for working on this.
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).
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.
@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.
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!