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

CL7777's avatar
CL7777
Helper III
5 years ago

I appreciate all of your help, I cannot use power query to make the table because my data set is created in Dax in BI using a summarize statement and is millions of lines. but I am almost there, one last thing I need help with to get this figured out. I created a table in Dax that has all the dates and data in there. it looks like this now (see sample table below)

 

I tried using your lastnonblank value measure statement to create the total value (QOH * Cost) for the last non-blank row in the table below (table called "temp table"). Im not getting values where the QOH and Cost is blank, in other words, its not retrieving the last non blank value of these and multiplying them together. Here is what Im using for the measure, what am I doing wrong?

 

Measure 2 = LASTNONBLANKvalue('temp table'[Last Day of Month],SUMX('temp table', 'temp table'[Cost]*'temp table'[QOH]))

 

Last Day of MonthPart NumberPlantQOHCost
10/31/2020AMfgSys  
9/30/2020AMfgSys  
8/31/2020AMfgSys  
7/31/2020AMfgSys  
6/30/2020AMfgSys  
5/31/2020AMfgSys  
4/30/2020AMfgSys  
3/31/2020AMfgSys  
2/29/2020AMfgSys  
1/31/2020AMfgSys  
12/31/2019AMfgSys714.37
11/30/2019AMfgSys  
10/31/2019AMfgSys744.43
9/30/2019AMfgSys  
8/31/2019AMfgSys  
7/31/2019AMfgSys764.43
6/30/2019AMfgSys824.43
5/31/2019AMfgSys864.43
4/30/2019AMfgSys  
3/31/2019AMfgSys  
2/28/2019AMfgSys984.43
1/31/2019AMfgSys  
10/31/2020BMfgSys  
9/30/2020BMfgSys  
8/31/2020BMfgSys18222.12
7/31/2020BMfgSys19222.12
6/30/2020BMfgSys  
5/31/2020BMfgSys20222.12
4/30/2020BMfgSys  
3/31/2020BMfgSys14222.12
2/29/2020BMfgSys  
1/31/2020BMfgSys4222.12
12/31/2019BMfgSys  
11/30/2019BMfgSys7242.40
10/31/2019BMfgSys8242.40
9/30/2019BMfgSys  
8/31/2019BMfgSys  
7/31/2019BMfgSys5242.40
6/30/2019BMfgSys7242.40
5/31/2019BMfgSys15242.40
4/30/2019BMfgSys8242.40
3/31/2019BMfgSys  
2/28/2019BMfgSys12242.40
1/31/2019BMfgSys13242.40
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