creating a measure that adds in implicit missing data

I am struggling to create a measure that sums the product of the EOM cost * EOM QOH in the table (attached below) including end of month dates that are not present. I have multiple part numbers in a list with all end-of-month dates where transactions occurred. For months where no transactions occurred, we still have a quantity on hand (QOH) and cost from the previous month that needs to be included in the measure. For example, in the table below, the measure for part number A should include the months where the data is missing, in other words for part A, the measure should equal:

8*84 (jan 2018) + 7*84 (Feb 2018) + 7*84 (no value for March 2018 so use prev non zero value from Feb) + 7*84 (no value for April 2018 so use previous non-zero value from Feb) + 7*84 (no value for May 2018 so use previous non zero value for Feb) + 6*84 (jun 2018) + 5*84 (jul 2018) + 5*84 (no value for Aug 2018 so use previous non zero value for Jul) + 4*84 (sept 2018) + ...... etc...

I want to do this for each part number.. so part number B would have the same type of calculation associated with it.

Any help would be MUCH appreciated,

DataTable

End of month	Part Number	EOM QOH	EOM Cost
1/31/2018	A	8	$84
2/28/2018	A	7	$84
6/30/2018	A	6	$84
7/31/2018	A	5	$84
9/30/2018	A	4	$84
3/31/2019	A	8	$84
4/30/2019	A	7	$84
7/31/2019	A	4	$84
9/30/2019	A	3	$84
11/30/2019	A	2	$90
3/31/2018	B	22	$68
4/30/2018	B	20	$68
5/31/2018	B	17	$68
6/30/2018	B	15	$68
7/31/2018	B	13	$68
9/30/2018	B	10	$68
11/30/2018	B	7	$68
2/28/2019	B	16	$68
3/31/2019	B	15	$68
6/30/2019	B	21	$68
7/31/2019	B	19	$68
8/31/2019	B	16	$68
9/30/2019	B	12	$68
12/31/2019	B	22	$70

Need Help

Anonymous

5 years ago

Hi CL7777 ,

You can create a measure as below:

Measure = 
VAR _curdate =
    MAX ( 'temp table'[Last Day of Month] )
VAR _curpart =
    MAX ( 'temp table'[Part Number] )
VAR _predate =
    CALCULATE (
        MAX ( 'temp table'[Last Day of Month] ),
        FILTER (
            ALL ( 'temp table' ),
            'temp table'[Part Number] = _curpart
                && 'temp table'[Last Day of Month] < _curdate
                && NOT ( ISBLANK ( 'temp table'[QOH] ) )
                && NOT ( ISBLANK ( 'temp table'[Cost] ) )
        )
    )
VAR _prevalue =
    CALCULATE (
        MAX ( 'temp table'[QOH] ) * MAX ( 'temp table'[Cost] ),
        FILTER (
            ALL ( 'temp table' ),
            'temp table'[Part Number] = _curpart
                && 'temp table'[Last Day of Month] = _predate
        )
    )
RETURN
    IF (
        ISBLANK ( MAX ( 'temp table'[Cost] ) ) && ISBLANK ( MAX ( 'temp table'[QOH] ) ),
        _prevalue,
        MAX ( 'temp table'[QOH] ) * MAX ( 'temp table'[Cost] )
    )