Help needed: Carry over balance from previous month

[aibrahim]

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

[aibrahim]

Can anyone else help here? I don't seem to find a solution 

[DoeJohn]

Hi Aibrahim,

Just wondering if the solution in message #2 works? I'm confused with your message #17 that says you don't seem to find a solution.

BR,
John

[aibrahim]

Thank you so much @v-kkf-msft! I'm getting really close to "done"! So now I've replaced the file with an updated file, and there seems to be an issue with monthly allowance / budget for new months. For example the new sheet I updated has new month (July) and it's not showing calculations for this month since its monthly allowance isn't defined in the table. 

How do I make it dynamic to show 10 hours every month (UNLESS hardcoded/ changed manually)?

[v-kkf-msft]

Hi @aibrahim ,

Does you established a Both relationship between the Calendar and monthly budget tables?

Best Regards,
Winniz

[aibrahim]

@v-kkf-msft yes it's there. I'm also attaching the new file with the additional month. https://www.dropbox.com/s/0fris16bv10kteq/LOH__BVC_report.rar?dl=0

[v-kkf-msft]

Hi @aibrahim ,

If you want to change the monthly budget table dynamically, please use the following formula.

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 ) )

Best Regards,
Winniz

[aibrahim]

@v-kkf-msft I tried it and it's showing a werid starting balance that's messing the entire calculations. Can you share the updated powerbi file? Maybe I did something wrong. 

Thanks for all the help. 

[v-kkf-msft]

Hi @aibrahim ,

It looks like this is because you have null value in the date column of table LOH__BVC_report. Please remove the empty rows in Power Query to ensure that there are no empty row in the monthly budget table.

Best Regards,
Winniz

[aibrahim]

@v-kkf-msft I'm sorry to keep dragging this but it just doesn't seem stable. I added one row for august (10 hours) to see if the new month is going to automatically populate and it didn't. Please see below. 

I however see it populated on the table: 

Any thoguhts why this is happening?

[v-kkf-msft]

Hi @aibrahim ,

Because the formula for the Calendar table is CALENDAR ( DATE ( 2022, 1, 1 ), TODAY () ), which means that the Calendar table does not contain the August date, this results in a balnk value in the first row.

You can try adding the Calendar table to August as well and try again.

Best Regards,
Winniz

[aibrahim]

@v-kkf-msft Thanks. I've been testing this and it looks like there's a problem with Strarting balance now. It got broken at one point. 

When you first provided the starting balance formula it was working as expected, but now it's not with all the recent updates. I've been troubleshooting. 

If you look at the latest file you shared (6), April has 11.42 hours remaining. May should be 10 (allowance of hours) + hours Remaining balance of previous month which is 11.42. So May starting balance should be 10+11.42 = 21.42 and not 19.75

Same for June, starting balance should be 18.75 + 10 = 28.75 and not 19

I'm not sure where exactly it broke. 

[aibrahim]

@v-kkf-msft any thoughts here? Thanks 

[aibrahim]

Hi @v-kkf-msft  just wanted to follow up here 

[aibrahim]

@v-kkf-msft The only thing I noticed is that you're using two row data / files (LOH__BV_report2.csv and Redstage_BV Copy - Calculations.xlsx), but the second file is really just for preview / example. I didn't want to use it in calculation as a row file. 

I only want to use one source which is LOH__BV_report2.csv. Can you pelase advise? 

The thank you so much! It looks like this is going to work

[v-kkf-msft]

Hi @aibrahim ,

I made some changes, please try these measures.

Credit / Bonus = 
CALCULATE (
    SUM ( 'LOH__BV_report2'[Time (Hours)] ),
    'LOH__BV_report2'[Task] = "Credit"
)

Starting balance = 
VAR PreMonth =
    CALCULATE (
        SUM ( 'monthly budget'[Allowance of Hours] )
            - SUM ( 'LOH__BV_report2'[Time (Hours)] ) + [Credit / Bonus],
        PREVIOUSMONTH ( 'Calendar'[Date] )
    )
RETURN
    IF (
        PreMonth >= 0,
        SUM ( 'monthly budget'[Allowance of Hours] ) + PreMonth,
        SUM ( 'monthly budget'[Allowance of Hours] )
    )

Remaining Balance = [Starting balance] - SUM ( 'LOH__BV_report2'[Time (Hours)] ) + [Credit / Bonus]

Best Regards,
Winniz

[aibrahim]

@v-kkf-msft  it looks like we don't have a resolution here

[v-kkf-msft · Accepted solution]

Hi @aibrahim ,

Please create a Calendar table and these measures.

Credit / Bonus = 
CALCULATE (
    SUM ( 'Log of Hours'[Time (Hours)] ),
    'Log of Hours'[Task] = "Credit"
)

Starting balance = 
VAR PreMonth =
    CALCULATE (
        SUM ( 'monthly budget'[Allowance of Hours] )
            - SUM ( 'Log of Hours'[Time (Hours)] ) + [Credit / Bonus],
        PREVIOUSMONTH ( 'Calendar'[Date] )
    )
RETURN
    IF (
        PreMonth >= 0,
        SUM ( 'monthly budget'[Allowance of Hours] ) + PreMonth,
        SUM ( 'monthly budget'[Allowance of Hours] )
    )

Remaining Balance = [Starting balance] - SUM ( 'Log of Hours'[Time (Hours)] ) + [Credit / Bonus]

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.