Forum Discussion

CL7777's avatar
CL7777
Helper III
5 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    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] )
    )

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
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] )
    )

Best Regards

Rena

CL7777's avatar
CL7777
Helper III
5 years ago

Thank you so much, that is exactly what I was looking for ! much appreciated