creating a measure that sums another measure that fills in data missing on a table

[CL7777]

Hi all,

I consider myself an intermediate DAX user but I am struggling with this one.. In the table below, I created a measure to calculate the missing QOH values with the last non blank value that occurred in time. So, for example, for part A and plant FL, the value of this measure for the missing QOH for Feb through Apr of 2020 would be 2 (the last non blank value that occurred in time).

End of Month	part number	plant	QOH
1/31/2020	A	FL	2
2/28/2020	A	FL
3/31/2020	A	FL
4/30/2020	A	FL
5/31/2020	A	FL	4
6/30/2020	A	FL	6
7/31/2020	A	FL
8/31/2020	A	FL
9/30/2020	A	FL
10/31/2020	A	FL
11/30/2020	A	FL
12/31/2020	A	FL	10
1/31/2020	A	CA	1
2/28/2020	A	CA	2
3/31/2020	A	CA
4/30/2020	A	CA
5/31/2020	A	CA	5
6/30/2020	A	CA
7/31/2020	A	CA
8/31/2020	A	CA	8
9/30/2020	A	CA
10/31/2020	A	CA
11/30/2020	A	CA
12/31/2020	A	CA	2
1/31/2020	B	FL	11
2/28/2020	B	FL
3/31/2020	B	FL
4/30/2020	B	FL
5/31/2020	B	FL	13
6/30/2020	B	FL
7/31/2020	B	FL
8/31/2020	B	FL
9/30/2020	B	FL	4
10/31/2020	B	FL	5
11/30/2020	B	FL
12/31/2020	B	FL	8
1/31/2020	B	CA	22
2/28/2020	B	CA
3/31/2020	B	CA	24
4/30/2020	B	CA	11
5/31/2020	B	CA
6/30/2020	B	CA
7/31/2020	B	CA
8/31/2020	B	CA	13
9/30/2020	B	CA
10/31/2020	B	CA
11/30/2020	B	CA	6
12/31/2020	B	CA	7

Here is the measure that I created to do this:

xpredateQOH =

VAR currentpart = MAX('tabletouse'[Part Number])

VAR currentplant = MAX('tabletouse'[Plant])

VAR currentdate = MAX('tabletouse'[Last Day of Month])

VAR predate = CALCULATE (

                               MAX ( 'tabletouse'[Last Day of Month] ),

                              FILTER

                                    (ALL ( 'tabletouse' ),

                                         'tabletouse'[Part Number] = currentpart

                                   && 'tabletouse'[Plant] = currentplant

                                   && 'tabletouse'[Last Day of Month] <= currentdate

                                   && NOT ( ISBLANK ( 'tabletouse'[QOH] ) )

                                  && NOT ( ISBLANK ( 'tabletouse'[Cost] ) )

                        )

               )

RETURN

     CALCULATE (

              MAX ( 'tabletouse'[QOH] ),

              FILTER

                  (ALL ( 'tabletouse' ),

                            'tabletouse'[Part Number] = currentpart

                      && 'tabletouse'[Plant] = currentplant

                      && 'tabletouse'[Last Day of Month] = predate

                      && NOT ( ISBLANK ( 'tabletouse'[QOH] ) )

                      && NOT ( ISBLANK ( 'tabletouse'[Cost] ) )

               )

   )

*********************

my problem comes in when I want to use this measure in a measure.. For example, I want to sumx over a subset of date values the values of the above measure for xpredateQOH.. In other words something like: SUMX(tableofDates, xpredateQOH). It does not work.. this is because it doesnt recognize the fact that each of the plants and part numbers have their own xpredateQOH.. If I select a specific plant and a partnumber from a slicer, the calculation works fine, but when I try to sum the xpredateQOH without specifying a particular plant and part number, it picks the max plant and max part number (in the whole data set) and calculates everything based on that part and plant. I want it to add (sumx) over those date values the total xpredateQOH for each part for each plant separately and add those together if there is no filters on them. I want to get an annual total quantity on hand of all parts in all plants.

any help would be much appreciated, I have spend an inordinate amount of time on this already.

[CL7777]

Hi DataZoe,

I have a quick followup with this post. My model is running very slow with this code, because I have a measure nested in an iterator. and the file is large (2 million rows). Is there a way to do this without putting a measure into an iterator?

[DataZoe]

@CL7777 This is a good point! Usually at this point I would see if I could push the problem back towards the source. I'm not sure how to do this in Power Query (M), which is one step back. So I'd go another step back, what is the source of the 2 million rows of data? Is it a SQL database?

[DataZoe · Accepted solution]

@CL7777 Please try these measures:

QOH With Missing =
VAR _d =
    MAX ( 'Table'[End of Month] )
VAR _p =
    MAX ( 'Table'[plant] )
VAR _s =
    MAX ( 'Table'[part number] )
RETURN
    IF (
        ISBLANK ( SUM ( 'Table'[QOH] ) ),
        CALCULATE (
            LASTNONBLANKVALUE ( 'Table'[End of Month], SUM ( 'Table'[QOH] ) ),
            REMOVEFILTERS ( 'Table' ),
            'Table'[End of Month] < _d,
            'Table'[plant] = _p,
            'Table'[part number] = _s
        ),
        SUM ( 'Table'[QOH] )
    )

then to get the total with the filled in values:

Total QOH with Missing = SUMX('Table',[QOH With Missing])

[CL7777]

Worked like a charm.. thank you SO much