DAX Support on budget billing amount

[TomKelly]

Hi All,

I am working on a report that requires me to calculate the amount that needs to be billed for each project, specifically for each budget. However, I’m struggling to build out the calculation such that it takes into account the context of the previous budgets for each row. I’d appreciate some help with this.

Here is some example data:

Project Name	Budget Index	Hours Budgeted	Hours worked to date	Billed hours	Unbilled hours	Amount to bill
Café Project 2024	1	100	230	100	130	0
Café Project 2024	2	100	230	100	130	100
Café Project 2024	3	100	230	100	130	30
Café Project 2024	4	100	230	100	130	0

What I’m looking to create is the calculation for the highlighted ‘Amount to Bill’ field. This field should show exactly what amount should be billed to each budget, using what has already been billed (Billed Hours) to understand whether the budget has already been ‘used up’ and taking into consideration what the budget for that row is (Hours Budgeted), such that it doesn’t bill more than the budget for that row.

The tricky part I’ve struggled with is when the amount to bill runs over multiple budgets, and showing only the remaining amount for the ‘currently active’ budget – in this case, Budget 3 at 30.

This is what I’ve created so far, but I am not such how to consider the outcome of this calculation from the previous record.

Amount to Bill =
   VAR CurrentBudget = 'Table'[Hours Budgeted]
   VAR PreviousBilled =
   CALCULATE(
      SUM('Table'[Billed Hours]),
      FILTER(
         'Table',
         'Table'[Project Name] = EARLIER('Table'[Project Name]) &&
         'Table'[Budget Index] < EARLIER('Table'[Budget Index])
      )
   )
   VAR RemainingHours = 'Table'[Hours Worked to Date] - PreviousBilled

RETURN
   IF(RemainingHours > CurrentBudget, CurrentBudget, RemainingHours)

 I appreciate the support from this community. This is my first post, so i'm missing anything feel free to let me know and I can add it. 

[BeaBF · Accepted solution]

@TomKelly Try with:

Amount to Bill =
VAR CurrentBudget = 'Table'[Hours Budgeted]
VAR CurrentWorked = 'Table'[Hours Worked to Date]
VAR PreviousBilled =
CALCULATE(
SUM('Table'[Billed Hours]),
FILTER(
'Table',
'Table'[Project Name] = EARLIER('Table'[Project Name]) &&
'Table'[Budget Index] < EARLIER('Table'[Budget Index])
)
)
VAR RemainingHours = CurrentWorked - PreviousBilled
VAR AmountToBill =
IF(RemainingHours > CurrentBudget, CurrentBudget, RemainingHours)

RETURN
MAX(0, AmountToBill)

BBF

[ValtteriN · Accepted solution]

Hi,

I think this is a good use case for visual calculations. E.g. 

Versus previous = VAR RemainingHours =  RUNNINGSUM([Hours Budgeted])

return

[Hours worked to date]-

RemainingHours

Amount to bill1 =

var _r =

PREVIOUS([Versus previous])-IF([Versus previous]<0,0,[Versus previous])

RETURN

IF(_r<0,0,_r)



By using these two here is the end result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous · Accepted solution]

Hi @TomKelly 

Thanks for the reply from BeaBF and ValtteriN , please allow me to provide another insight:

Please try modifying the formula to the following:

Amount to Bill = 
   VAR CurrentBudget = 'Table'[Hours Budgeted]
   VAR PreviousBilled =
   CALCULATE(
      SUM('Table'[Billed Hours]),
      FILTER(
         'Table',
         'Table'[Project Name] = EARLIER('Table'[Project Name]) &&
         'Table'[Budget Index] < EARLIER('Table'[Budget Index])
      )
   )
   VAR RemainingHours = IF(PreviousBilled <> BLANK(), 'Table'[Hours Worked to Date] - PreviousBilled, 0)
RETURN
IF(RemainingHours <= 0, 0, IF(RemainingHours > CurrentBudget, CurrentBudget, RemainingHours))

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN · Accepted solution]

Hi,

I think this is a good use case for visual calculations. E.g. 

Versus previous = VAR RemainingHours =  RUNNINGSUM([Hours Budgeted])

return

[Hours worked to date]-

RemainingHours

Amount to bill1 =

var _r =

PREVIOUS([Versus previous])-IF([Versus previous]<0,0,[Versus previous])

RETURN

IF(_r<0,0,_r)



By using these two here is the end result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[BeaBF · Accepted solution]

@TomKelly Try with:

Amount to Bill =
VAR CurrentBudget = 'Table'[Hours Budgeted]
VAR CurrentWorked = 'Table'[Hours Worked to Date]
VAR PreviousBilled =
CALCULATE(
SUM('Table'[Billed Hours]),
FILTER(
'Table',
'Table'[Project Name] = EARLIER('Table'[Project Name]) &&
'Table'[Budget Index] < EARLIER('Table'[Budget Index])
)
)
VAR RemainingHours = CurrentWorked - PreviousBilled
VAR AmountToBill =
IF(RemainingHours > CurrentBudget, CurrentBudget, RemainingHours)

RETURN
MAX(0, AmountToBill)

BBF