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
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 !
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
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 Labels | Volume Forecast | Volume Forecast Max Date | Volume 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 Labels | Volume Forecast | Volume Forecast Max Date | Volume 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