Compare total for 2 categories based on a weekly basis

[darentengdrake]

Hi,

I have a formula that would choose forecast if it's greater than demand and choose demand if it's greater than forecast.

IF([Forecast]>[Demand],[Forecast],[Demand])

Forecast measure is sum(forecastamount) and Demand measure is sum(demandamount). They are in the same table and same column. Forecast has an ID of "FC" and Demand has an ID of "DM".

What I want to try to do next is to have it compare the total for each week, and choose the greater amount based on each respective week only, however, the formula I have compares the running total instead.

How should I alter the formula such that it will choose between Forecast or Demand, based on the weekly total instead of the running total, meaning that chosen one (between Forecast and Demand) for previous week should not affect the chosen one for current week?

Thanks!

Daren

[v-chuncz-msft]

@darentengdrake 

You may try using SUMX.

https://community.powerbi.com/t5/Desktop/Power-BI-desktop-not-calculating-total-for-measure-in-Matri...

[sturlaws]

Hi @darentengdrake ,

if you want to show this pr week, try this formula

measure =
VAR _forecast =
    CALCULATE ( SUM ( [Forecast] ); FILTER ( table; ID = "FC" ) )
VAR _demand =
    CALCULATE ( SUM ( [Forecast] ); FILTER ( table; ID = "DM" ) )
RETURN
    IF ( _forecast > _demand; _forecast; _demand )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[darentengdrake]

Hi @sturlaws ,

I've tried your measure, but the outcome of it is the same as the one that I am using. Somehow, the measure is comparing the running total of the demand with the running total of the forecast, which is not wanted as I need it to compare the total of demand for week 1 to the total of forecast for week 1, and so on. 

Daren

[sturlaws]

sorry, I was unclear with the code. the SUM should be of the column value, not your running total measure:

measure =
VAR _forecast =
    CALCULATE ( SUM ( 'Table'[Forecast] ); FILTER ( table; 'Table'ID = "FC" ) )
VAR _demand =
    CALCULATE ( SUM ( 'Table'[Forecast] ); FILTER ( table; 'Table'ID = "DM" ) )
RETURN
    IF ( _forecast > _demand; _forecast; _demand )

Or are you column already calculated as running totals?

[darentengdrake]

Hi @sturlaws 

This was exactly what I did (instead of the measure, I used the columns) but it shows me the same results that I currently have.

Daren

[sturlaws]

Could you share your report? Or if it contains data you cannot share, create a sample report reflecting your report?

[darentengdrake]

Hi @sturlaws 

It will take some time for me to create a sample report. Before I go to that, I figure it probably could be this formula that is not right

Net RT =
CALCULATE(
[Net Available],
FILTER(
ALLEXCEPT ( Forecast, Forecast[ITEMID] ),
Forecast[Date] <= MAX(Forecast[Date])))

The [Net Available] measure is just on hand inventory+purchases-demandfinal

Here is an example of the measure not adding up:

On Hand = 0

Purchases = 500

DemandFinal = 2000

Last Rolling Total = -200

The net available is 0 + 500 - 2000 = -1500. The last rolling total was -200 We are trying to figure out why the Net RT(formula above that is the rolling total) is not showing -1700 (which is the net available + the last rolling total), but instead showing 0, which I believe it could potentially be the Net RT formula.

Daren