Actuals Total vs Forecast per Week

[joshua1990]

Hi experts!

I am no sure how this concept is called but I need to calculate the how much of the actuals fall into WK01, WK02 etc. Forecast.

The table is presented in the following way:

Product	WK01	WK02	WK03	Actuals
A	550	40	700	600
B	600	300	300	500
C	0	500	0	250

Now I need to calculcate the amout per week that is covered by the actuals like this:

Product	WK01	WK02	WK03	Actuals	Actuals WK01	Actuals WK02	Actuals WK03
A	550	40	700	600	550	40	10
B	600	300	300	500	500	0	0
C	0	500	0	250	0	250	0

I would need a measure for Actuals WK01, WK02 and WK03.

How would you calculate ?

[v-ssriganesh]

Hi @joshua1990,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[Sahir_Maharaj]

Hello @joshua1990,

Could you please try this approach:

-- Actuals WK01
Actuals WK01 = 
VAR Actuals = 'Table'[Actuals]
VAR WK01 = 'Table'[WK01]
RETURN MIN(Actuals, WK01)

-- Actuals WK02
Actuals WK02 = 
VAR Actuals = 'Table'[Actuals]
VAR UsedWK01 = MIN(Actuals, 'Table'[WK01])
VAR Remaining = Actuals - UsedWK01
VAR WK02 = 'Table'[WK02]
RETURN MIN(Remaining, WK02)

-- Actuals WK03
Actuals WK03 = 
VAR Actuals = 'Table'[Actuals]
VAR UsedWK01 = MIN(Actuals, 'Table'[WK01])
VAR RemainingAfterWK01 = Actuals - UsedWK01
VAR UsedWK02 = MIN(RemainingAfterWK01, 'Table'[WK02])
VAR Remaining = RemainingAfterWK01 - UsedWK02
VAR WK03 = 'Table'[WK03]
RETURN MIN(Remaining, WK03)

[v-ssriganesh]

Hello @joshua1990,

Could you please confirm if your query have been resolved? If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you.

[v-ssriganesh · Accepted solution]

Hello @joshua1990 ,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario using sample data, and I was able to achieve the expected result you're aiming for where the Actuals are distributed across WK01, WK02, WK03, etc., based on forecast priority.

I created calculated columns to distribute the Actuals across the forecast weeks sequentially, reducing the remaining actuals each step.

DAX i have used:

• Actuals WK01

Actuals WK01 =

VAR A = ForecastData[Actuals]

VAR W1 = ForecastData[WK01]

RETURN IF(A >= W1, W1, A)

• Actuals WK02

Actuals WK02 =

VAR A = ForecastData[Actuals]

VAR W1 = ForecastData[WK01]

VAR W2 = ForecastData[WK02]

VAR Remaining = A - W1

RETURN IF(Remaining > 0, IF(Remaining >= W2, W2, Remaining), 0)

• Actuals WK03

Actuals WK03 =

VAR A = ForecastData[Actuals]

VAR W1 = ForecastData[WK01]

VAR W2 = ForecastData[WK02]

VAR W3 = ForecastData[WK03]

VAR Remaining = A - W1 - W2

RETURN IF(Remaining > 0, IF(Remaining >= W3, W3, Remaining), 0)

You can easily extend this same logic for additional weeks like WK04, WK05, etc., by continuing the same pattern.

I’m attaching the output screenshot and .pbix file for your reference:

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @joshua1990,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[ahmetyilmaz]

Hi, can you explain how you obtained the data in table 2?