creating a measure that adds in implicit missing data

[CL7777]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[CL7777]

Thank you very much, I opened the pbix file but the table created looks correct, but there is no measure in the pbix that computes what I am looking for. Am I missing something?

[AllisonKennedy]

Thanks @Ashish_Mathur  for the sample file.

@CL7777 

Try this measure: 

LastNonBlank =

VAR _ThisDate = MAX('Calendar'[Date])

VAR _LastData = MAXX(FILTER(ALL(Data[End of month ]), Data[End of month ]<=_ThisDate), Data[End of month ])

RETURN

CALCULATE(LASTNONBLANKVALUE('Calendar'[Date],SUMX(Data, Data[EOM Cost]*Data[EOM QOH])), ALL('Calendar'[Date]),'Calendar'[Date]<=_ThisDate)

To see the solution from @Ashish_Mathur  you will need to look at Power Query editor- so click Transform Data in the Home tab in the ribbon. He has created a new table with a copy of the 'Data' so that there is a row for every month. Depending on how big your 'Data' table is, this might slow the refresh, but should make filtering in the report view faster.

I have uploaded the file with my edits below my signature here. 

[AllisonKennedy]

@CL7777  

Otherwise, to do this with a Measure, you will need to create the table as a visualization: 

Total Product Cost = SUMX(Data, Data[EOM Cost]*Data[EOM QOH])

LastNonBlank =

VAR _ThisDate = MAX('Calendar'[Date])

VAR _LastData = MAXX(FILTER(ALL(Data[End of month ]), Data[End of month ]<=_ThisDate), Data[End of month ])

RETURN

CALCULATE(LASTNONBLANKVALUE('Calendar'[Date],SUMX(Data, Data[EOM Cost]*Data[EOM QOH])), ALL('Calendar'[Date]),'Calendar'[Date]<=_ThisDate)

[CL7777]

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 Month	Part Number	Plant	QOH	Cost
10/31/2020	A	MfgSys
9/30/2020	A	MfgSys
8/31/2020	A	MfgSys
7/31/2020	A	MfgSys
6/30/2020	A	MfgSys
5/31/2020	A	MfgSys
4/30/2020	A	MfgSys
3/31/2020	A	MfgSys
2/29/2020	A	MfgSys
1/31/2020	A	MfgSys
12/31/2019	A	MfgSys	71	4.37
11/30/2019	A	MfgSys
10/31/2019	A	MfgSys	74	4.43
9/30/2019	A	MfgSys
8/31/2019	A	MfgSys
7/31/2019	A	MfgSys	76	4.43
6/30/2019	A	MfgSys	82	4.43
5/31/2019	A	MfgSys	86	4.43
4/30/2019	A	MfgSys
3/31/2019	A	MfgSys
2/28/2019	A	MfgSys	98	4.43
1/31/2019	A	MfgSys
10/31/2020	B	MfgSys
9/30/2020	B	MfgSys
8/31/2020	B	MfgSys	18	222.12
7/31/2020	B	MfgSys	19	222.12
6/30/2020	B	MfgSys
5/31/2020	B	MfgSys	20	222.12
4/30/2020	B	MfgSys
3/31/2020	B	MfgSys	14	222.12
2/29/2020	B	MfgSys
1/31/2020	B	MfgSys	4	222.12
12/31/2019	B	MfgSys
11/30/2019	B	MfgSys	7	242.40
10/31/2019	B	MfgSys	8	242.40
9/30/2019	B	MfgSys
8/31/2019	B	MfgSys
7/31/2019	B	MfgSys	5	242.40
6/30/2019	B	MfgSys	7	242.40
5/31/2019	B	MfgSys	15	242.40
4/30/2019	B	MfgSys	8	242.40
3/31/2019	B	MfgSys
2/28/2019	B	MfgSys	12	242.40
1/31/2019	B	MfgSys	13	242.40

[v-yiruan-msft · Accepted solution]

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]

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

[Ashish_Mathur]

You are welcome Allison.

[Ashish_Mathur]

Hi,

I am not sure of whom you are replying to.  In the file download link that i shared, there is defenitely a measure.  Please check again.

[CL7777]

I really appreciate your response. I do not see a measure that calculates what I need. I see a table called "all months for each part" which has what I need but I do not see how that table was calculated. The only measure I show is for a variable called inventory value which just mulitplies EOM cost times EOM QOH.. The table is exactly what I want, but I do not see the code that created that table. 

[Ashish_Mathur]

Hi,

Has @AllisonKennedy answered your question?  I have used the Query Editor to transform the data.

[AllisonKennedy]

You can find the code of @Ashish_Mathur in Power Query Editor, click on Advanced Editor in Power Query:

let
Source = Data,
#"Removed Columns" = Table.RemoveColumns(Source,{"EOM QOH", "EOM Cost"}),
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Part Number"}, {{"Start date", each List.Min([#"End of month "]), type nullable date}, {"End date", each List.Max([#"End of month "]), type nullable date}}),
#"Calculated Start of Month" = Table.TransformColumns(#"Grouped Rows",{{"Start date", Date.StartOfMonth, type date}}),
#"Added Custom" = Table.AddColumn(#"Calculated Start of Month", "Month Span", each (12 * (Date.Year([End date]) - Date.Year([Start date])))
+ (Date.Month([End date]) - Date.Month([Start date]))
+ (if Date.Day([End date]) < Date.Day([Start date])
then -1
else 0
)
+ 1),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month List", each List.Numbers(
1,
[Month Span]
)),
#"Expanded Month List" = Table.ExpandListColumn(#"Added Custom1", "Month List"),
#"Added Custom2" = Table.AddColumn(#"Expanded Month List", "Month End Date", each Date.EndOfMonth(
Date.AddMonths(
[End date],
0 - [Month Span] + [Month List]
)
)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"Start date", "End date", "Month Span", "Month List"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Month End Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Part Number", "Month End Date"}, Data, {"Part Number", "End of month "}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"EOM QOH", "EOM Cost"}, {"EOM QOH", "EOM Cost"}),
#"Sorted Rows" = Table.Sort(#"Expanded Data",{{"Part Number", Order.Ascending}, {"Month End Date", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"EOM QOH", "EOM Cost"})
in
#"Filled Down"

[AllisonKennedy]

Do you have a Date table?

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

That will help give you all dates that are not present and you can calculate the measure over the date table, and use the Date[Month] in the matrix to get the result you want. Then you can use LASTNONBLANKVALUE to help get the result you want.

[CL7777]

Thanks for your response. I do have a date table but im still unclear how I use the last non blank function to create my desired results. would you be able to provide me a sample measure that I could try?