DAX - Measure to get last non blank reported value in stock

[rlsantos]

Hi everyone!
Does anyone know how to solve this problem?

I developed a measure that brings up the last position of the stock, and it works perfectly, but the grand total is incorrect.

This is my measurement:

Last stock:=
VAR MaxData = MAX(f_Stock[Week No.])
RETURN
CALCULATE([Sum of Stock];FILTER(f_Stock;f_StockS[Week No.]=MaxData))

In practice, it is according to the attached image, note that for each store the stock is correct, as it is bringing the data of the last informed date. But the grand total is incorrect. I found that the total is considering only the maximum date recorded, I need the total to add up all the stock considering the last release.

[tamerj1]

Hi @rlsantos 
You may create a new measure that corrects the results of the old one and use it in the pivot table instead of the old one

Last stock with correct totals :=
SUMX ( SUMMARIZE ( f_Stock, f_Stock[Store], f_StockS[Week No.] ), [Last stock] )

[Whitewater100]

Hello:

To avoid a lot of headaches down the road,

The best thing you should do is have a separate datetable. Connected to your fact table. Your f_Stock table should have a date associated with the reporting. You can connect that date to the date table,marked as such,with continuous dates spanning all of your data and beyond.

To obtain proper last inventory amount you would put a "helper column" in your Date Table. Due to multile stores there can be occasions where this can really help. I have named the Date Table DateTable, but most just name it Dates. Then this column is added to your Date Table.

In Date(calc col)   Rows of Inv = CALCULATE( NOT ISEMPTY(f_stock))

Your measure for EOM OnHandQty = LASTNONBLANKVALUE( DateTable[Date], [sum of stock]))

The measure for handling this across stores can be:

On Hand Total = CALCULATE(
                                           SUM( F_Stock[sumofstock]), 
                                            CALCULATETABLE( 

                                                LASTDATE(Datetable[Date]), 
                                              Datetable[rows of Inv] = TRUE))

[Tutu_in_YYC]

Hola rlsantos,

Use LASTNONBLANKVALUE to get the latest stock count and then use SUMX to sum up the stock counts for all the different stores.

Total Geral = 
SUMX(
   VALUES(f_Stock[Store]),
   LASTNONBLANKVALUE(DateTable[Date],[sum of Stock])
)

If you dont have DateTable[Date], use the column that has date in f_stock. It should also work with the column [Week No.] if the data type is number.

Let me know how it goes.

[Whitewater100]

Hi:

Have you tried the function LASTNONBLANKVALUE?

  end Stock = LASTNONBLANKVALUE( DateTable[Date], [sum of stock]))