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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Watch MrExcel Video

Forum statistics

Threads
1,090,191
Messages
5,412,972
Members
403,459
Latest member
acharnp

This Week's Hot Topics

Top