Incorrect result when working with Max Date

JanLofgren

New Member
Joined
Nov 19, 2019
Messages
1
I need some help as I get an incorrect result and I can not solve it.

I have a table with following fields:
- Ordertype ("Actual" or "Forecast")
- CustomerNumber
- OrderNumber
- DeliveryDate
- Plant
- Material
- Volume

I have following measures
- Sum of Volume = SUMX ( FactData; 'FactData'[Volume])
- Volume Forecast = CALCULATE ( DIVIDE ( [Sum of Volume];1000 ); 'FactData'[OrderType]="Forecast" )
- MaxDateActual = CALCULATE (max(FactData[DeliveryDate]);FactData[OrderType]="Actual")
- Volume Forecast Max Date = CALCULATE ([Volume Forecast];FILTER(MD_Dates;[Date]<=[MaxDateActual]))

I use the MaxDate Actual as I want to limit also the forecast volume to tha last day there is actual volume as the forecast volume is always for a full month.
When I filter on customer or on plant, I get no volume on those days where there is also no actual volume and I do not know why
Result
Row LabelsVolume ForecastVolume Forecast Max DateVolume Actual
02/11/2019
19​
19​
18​
04/11/2019
11​
11​
19​
06/11/2019
19​
19​
19​
08/11/2019
11​
11​
18​
09/11/2019
19​
11/11/2019
19​
12/11/2019
13​
13/11/2019
19​
14/11/2019
19​
15/11/2019
19​
Grand Total
130​
60​
112​

Tested when I put hardcoded the date in the measure 'Volume Forecast Max Date' (CALCULATE([Volume Forecast];FILTER(MD_Dates;[Date]<=DATE(2019;11;15)))), than it works !

Row LabelsVolume ForecastVolume Forecast Max DateVolume Actual
02/11/2019
19​
19​
18​
04/11/2019
11​
11​
19​
06/11/2019
19​
19​
19​
08/11/2019
11​
11​
18​
09/11/2019
19​
19​
11/11/2019
19​
12/11/2019
13​
13​
13/11/2019
19​
14/11/2019
19​
19​
15/11/2019
19​
19​
Grand Total
130​
130​
112​

When I use the MaxDateActual measure again (=CALCULATE([Volume Forecast];FILTER(MD_Dates;[Date]<=DATE(YEAR([MaxDateActual]);Month([MaxDateActual]);Day([MaxDateActual]))))), it fails.

Can somebody help me ?
Thanks in advance
Jan
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,223,275
Messages
6,171,122
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top