Hi,
I have a simple measure that gives me the final value where if Forecast is greater than Demand, use Forecast numbers, else use Demand numbers.
DemandFinal = if([currmo]=0,[Demand],IF([Forecast]>[Demand],[Forecast],[Demand]))
However, this does not work if it is not the current month. The numbers for all upcoming months are not correct as it is using the highest number that it has instead of comparing it by weeks. What I am trying to do below is to make the formula compare the Forecast and the Demand values per weekly basis, such that Week 1's results will not affect Week 2's results based on the formula below, however this is where I'm stuck at. Currently, this formula is getting the running totals of DemandFinal but I need it to take the values where DemandFinal successfully chooses the right number (Forecast or Demand) based on the comparison per weekly basis.
Net RT =
CALCULATE(
[DemandFinal],
FILTER(
ALLEXCEPT ( ForecastNet, ForecastNet[ITEM] ),
ForecastNet[Date] <= MAX(ForecastNet[Date])
)
To illustrate the problem I'm facing:
Week 1 Forecast: 1,000
Week 1 Demand: 3,000
Week 2 Forecast: 1,500
Week 2 Demand: 0
Week 3 Forecast: 1,000
Week 3 Demand: 0
The formula DemandFinal will take 3,000 for Week 1 and 2 instead of just for Week 1. What I need Net RT to do is compare Week 1 Forecast with Week 1 Demand, choose the higher number, compare Week 2 Forecast with Week 2 Demand, choose the higher number, and Week 3 Forecast with Week 3 Demand, and choose the higher number, which therefore the running total should be 3,000+1,500+1,000 = 5,500. Currently it is giving me 3,000 + 0 + 1,000 = 4,000.
Thanks!
