Running Totals Added Together to produce a forcast

[zl34567]

Hi, Im trying to add together 2 RT Totals but cant seem to figure it out 😞

I have a running total in one table for the Actuals (P6_PowerBI_Actual_Labor_Units)

Calculation:

P6_RT_Actual_Labor_Units =

CALCULATE(SUM(P6_PowerBI_Actual_Labor_Units[Value]),FILTER(ALLSELECTED(PowerBI_Date[Date]),PowerBI_Date[Date]<=MAX(P6_PowerBI_Actual_Labor_Units[Date])))

And another table for the remaining hours  (P6_PowerBI_Remaining_Labor)

Calculation:

P6_RT_Remaining_Labor =

CALCULATE(SUM(P6_PowerBI_Remaining_Labor[Value]),FILTER(ALLSELECTED(PowerBI_Date[Date]),PowerBI_Date[Date]<=MAX(P6_PowerBI_Remaining_Labor[Date])))

Both tables are joined together with a Date table

PowerBI_Date.

Current results if i sum both together.

Intended Result.

Any help would be appricated, Thanks in Advance.

[v-sgandrathi]

Hi @zl34567,

Has your issue been resolved?
If the response provided by @grazitti_sapna  and @Praful_Potphode  addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone. 

Thank you.

[v-sgandrathi]

Hi @zl34567,

Just checking in -- have you had a chance to review and try the provided solution? Kindly share the status whenever you get a chance.

Looking forward to your response.

[v-sgandrathi]

Hi @zl34567,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.Please let us know if there’s anything else we can do to help.
Thank you.

[grazitti_sapna]

Hi @zl34567 , 
You can try using below measure 

PK_PowerBI_Total_Forecast =
VAR ActualTotal =
CALCULATE(
SUM(PowerBI_Actual_Labor[Units]),
FILTER(
ALLSELECTED(PowerBI_Date[Date]),
PowerBI_Date[Date] <= MAX(PowerBI_Date[Date])
)
)
VAR RemainingTotal =
CALCULATE(
SUM(PowerBI_Remaining_Labor[Units]),
FILTER(
ALLSELECTED(PowerBI_Date[Date]),
PowerBI_Date[Date] <= MAX(PowerBI_Date[Date])
)
)
RETURN
ActualTotal + RemainingTotal

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[zl34567]

Thats Great Thank you,
@grazitti_sapna Would there be a way to stop the lines at their own MAX Dates?
Rather than running they through every date in the date table? The max end date would be equal to the max remaining date

[v-sgandrathi]

Hi @zl34567,

This issue happens because your current DAX only calculates running totals for dates found in both tables. If Actuals or Remaining Labor is blank, the cumulative line stops instead of continuing the total.

To fix this, use the COALESCE() function rather than IF(ISBLANK()).
COALESCE() will replace blanks with zero, so the cumulative forecast remains consistent even when dates are missing in one of the tables.

Here’s the revised measure to try:
RT Total Forecast =
VAR ActualRT = [P6_RT_Actual_Labor_Units]
VAR RemainingRT = [P6_RT_Remaining_Labor]
RETURN
COALESCE(ActualRT, 0) + COALESCE(RemainingRT, 0)

If you want the line to stop after the final available date (when both Actuals and Remaining are blank), you can extend it like this:

RT Total Forecast =
VAR ActualRT = [P6_RT_Actual_Labor_Units]
VAR RemainingRT = [P6_RT_Remaining_Labor]
VAR HasData =
    NOT ISBLANK([P6_PowerBI_Actual_Labor_Units[Value]]) ||
    NOT ISBLANK([P6_PowerBI_Remaining_Labor[Value]])
RETURN
IF(
    HasData,
    COALESCE(ActualRT, 0) + COALESCE(RemainingRT, 0)
)

This will Keep the cumulative forecast line continuous over all valid dates. Handle blanks gracefully. Stop automatically after the last available data point.

Thankyou and continue using Microsoft Fabric Community Forum.

[zl34567]

hI @v-sgandrathi , Thanks for the help when i copied it in the second dax code has an error,
have i missed something?

[v-sgandrathi]

Hi @zl34567,
Thank you for using Microsoft Fabric Communtiy forum.

This error occurs because DAX does not permit direct column references like [Table[Column]] within a measure. To resolve this, you should use an aggregation function, such as SUM(), within a CALCULATE() statement.

RT Total Forecast =
VAR ActualRT = [P6_RT_Actual_Labor_Units]
VAR RemainingRT = [P6_RT_Remaining_Labor]
VAR HasData =
NOT (
ISBLANK ( CALCULATE ( SUM ( P6_PowerBI_Actual_Labor_Units[Value] ) ) ) &&
ISBLANK ( CALCULATE ( SUM ( P6_PowerBI_Remaining_Labor[Value] ) ) )
)
RETURN
IF (
HasData,
COALESCE ( ActualRT, 0 ) + COALESCE ( RemainingRT, 0 )
)

Thank you.

[zl34567]

Thats Great Thank you 🙂

[Praful_Potphode]

hi @zl34567 
based on the information provided, you can try creating a visual calculation to show the information.

Please follow below steps:

1. Select your line chart
2. CLick on New visual Calcuation under Home Tab.

3.  Select Custom.

4.rename Calculation to total and write formula below.

ToTal = [P6_RT_Actual_Labor_Units]+[P6_RT_Remaining_Labor ]

  The output will look like below.

Please give Kudos or mark it as solution once resolved.

Thanks and Regards,

Praful

[zl34567]

Hi, Thanks for the reply, It seems to work if i have actuals on the same date but the rolling total seems to not carry on over the dates. Any ideas what i could be doing wrong?

[Praful_Potphode]

HI @zl34567 

you can handle it using IF condition.

ToTal = IF(ISBLANK([P6_RT_Actual_Labor_Units]),
BLANK(),//blank is an example you can put the output you want here
[P6_RT_Actual_Labor_Units]+[P6_RT_Remaining_Labor ]
)

this will stop the line when there are no actuals.you can experiment with these calculations based on what outcome you want.

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful