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
 

Forum statistics

Threads
1,077,721
Messages
5,335,816
Members
399,051
Latest member
WPO

Some videos you may like

This Week's Hot Topics

Top