topic Re: Actuals Total vs Forecast per Week in DAX Commands and Tips

Actuals Total vs Forecast per Week

joshua1990 — Mon, 10 Mar 2025 17:35:49 GMT

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 ?

Re: Actuals Total vs Forecast per Week

ahmetyilmaz — Mon, 10 Mar 2025 20:50:25 GMT

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

Re: Actuals Total vs Forecast per Week

v-ssriganesh — Fri, 11 Apr 2025 05:34:09 GMT

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.

Re: Actuals Total vs Forecast per Week

v-ssriganesh — Mon, 14 Apr 2025 11:37:10 GMT

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.

Re: Actuals Total vs Forecast per Week

Sahir_Maharaj — Mon, 14 Apr 2025 19:23:33 GMT

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)

Re: Actuals Total vs Forecast per Week

v-ssriganesh — Tue, 22 Apr 2025 17:30:05 GMT

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.

Re: Actuals Total vs Forecast per Week

v-ssriganesh — Fri, 25 Apr 2025 07:08:52 GMT

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.