Matrix with historical stock values per day, filling blanks with the most recent value

[Brandaris]

Hello,

Is seems probably a quite simple issue but I didn't succeed until now.

My goal is to make a Matrix or Linechart with the following variables:

* Date

* Product (group)

* Hostoric and Actual Inventory value on every selected day (with a date-table)

My model has (among others) the following tables:

- Product (ProductID, ProductName, ProductGroupName, etc)

- Calendar (date, week, Month, Year, etc)

- Stockmutations: (ProductID, Mutationdate, MutationAmount, NewStockAmount, Price)

If I calculate the Stockvalue (i.c. NewStockAmount * Price) and plot it in a Matrix, with the products in the Rows and the dates in the columns, it shows only the new stock value in date-cells there was actually a mutation on. The other cells stay blank.

For example: when there was a mutation on the 29th of june it returns an NewStockValue 100, on the 30th of june nothing changed (so there is no mutation date): the value of te NewStockValue = BLANK. Then on de 1th of July 20 pieces were sold (so there is a mutationdate now), the NewStockValue = 80.

My challenge is to make een measure (I think with creating variables) to fill the value on June 30th with the same value as on June 29th (i.c. 100). This is the most recent value before the reference-date (July 1st).

Thank you very much in advance for your help en tips

Kind regards,
Hans

[Brandaris · Accepted solution]

Hello again,

I managed to solve the problem and in hindsight it wasn't so complicated as it seemed to be 🙂

It was necessary to create a new table with GENERATE(Dates,Product_ID) and then add a third column called "Value" containing the measure "Stockvalue 1".

This is now the new matrix:

Right values in the cells, correct subtotals and grand totals and every calendar date displayed.

Thanks for thinking along.

Kind regards,
Brandaris

[v-yiruan-msft]

Hi @Brandaris ,

It's glad to hear that your problem has been resolved. And thanks for sharing your solution here. Could you please mark your post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours.  Thank you.

Best Regards

[Brandaris]

UPDATE:

I've found an interesting video on YouTube how to keep the most recent value with table.buffer

https://www.youtube.com/watch?v=S3X_HK7yl1w

So this is solved 🙂

[amitchandak]

@Brandaris , One of the ways is lastnonblankvalue

Calculate( LASTNONBLANKVALUE(Calendar[Date], Sumx(Stockmutations, [NewStockAmount]*[Price]) ), Filter(all(Calendar), Calendar[Date] <= max(Calendar[Date])))

other is cumulative

example of inventory 

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

[Brandaris]

Hello Amitchandak,

Thank you very much for your quick reaction!

It's a big step in the right direction. Great!

There's just one new challenge now: when there are two or more mutations on the same day, they will count together. Instead of that, only the last mutation has to be taken. In the mutationtable there is a date/time notation, but in the calendar table there is only a date notation. So there seems no way to filter this with calendar table.....

Is it possible to skip the oldest mutation on the same day in Power Query or will there possibly a more nice solution for this question?

Thank you again in advance!

Kind regards,

Hans

[Brandaris]

Keeping only the most recent value on the same day, is possible with table.buffer (see my post above).

The only thing I've to fix to get the right Matrix, is the Totals. The individual values per date for each article are good now, but the Totals per column are wrong and seem te be random. It's always the same amount of one of the individuals.......

Is this possible to solve....... In hope so 🙂

[v-yiruan-msft]

Hi @Brandaris ,

From your description, it seems that the total value of matrix is incorrect. You can refer to the content of the links below to try to solve your problem by creating another new measure base on current measure...

Power BI Shows Incorrect Measure Total? How to fix it?

Fixing Incorrect Totals in DAX

Monthly Target Met =
SUMX (
VALUES ( 'DateTable'[Month Year] ),
IF ( [Total Amount] > 3000000, 1 )
)

Why Your Total Is Incorrect – A Key Power BI Concept

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. What your matrix visual looks like? How did you set your matrix(Fields setting)? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Brandaris]

Dear Yingyinr,

Very nice of you to think along with me and even take a look at my simplified PBIX.

Because I'm not a so called Superuser, it's impossible for me to upload mij PBIX. Is there a possibility that I can send it to you by e-mail, (or share by Onedrive)?

I am very curious about your solution direction (if there is one 🙂 )

Kind regards,

Hans

[v-yiruan-msft]

Hi @Brandaris ,

Since you can't provide the sample pbix file, could you please provide some sample data(exclude sensitive data) involved in your visual with Text format and the field settings of your matrix with screenshot? Thank you.

Sample data:

Col1	Col2	Col3	Colx
xx	xx	xx	xx
xx	xx	xx	xx

Field settings

Best Regards

[Brandaris]

Hello Yingyinr,

Thank you very much again !

In the next pictures (screenshots) I'll show you the model, the data and the Matrix with the right "cell-values", but the wrong totals and also the measure I've used for it:

All dates in 2022Matrix (with wrong Totals)MeasureModelFact table (mutations)Product table

I hope this will be clear enough this way and  I'm looking forward with great interest to the possible solution to this apparently simple problem, which turns out to be more difficult than expected 🙂

Thank you very much in advance.

Kind regards,

Brandaris

[v-yiruan-msft]

Hi @Brandaris ,

You can create another new measure as below to get the correct total values base on the measure [Actual Stockvalue] and put this new measure on the matrix to replace the original measure [Actual Stockvalue]:

New measure =
SUMX (
    VALUES ( 'Products'[ProductGroup] ),
    SUMX ( VALUES ( 'Mutations'[ProductID] ), [Actual Stockvalue] )
)

Best Regards

[Brandaris]

Hello Yingyinr,

Thank you very much again for your possible solution.

I tried it out and unfortunately I've got the same results as my very first attempt: correct totals, but blank results in the stockvalue on the days when no change occurred.

Measure Stock Value 1 does that well, but then the totals are not correct.
With the measure Stock Value 2, the totals are correct, but the days on which no change has occurred are missing in the matrix and blank results are generated for the products that do not have a change on the days on which one or more other products have changed in terms of stock.

I'm afraid the reason is that the dates-table is used twice: 1st for the DAX formula for the measure and 2nd as a "filter" in the columns of the matrix. This may conflict....

I will take a closer look at both measures to see if there is a kind of "best of both worlds" combination.

KiStock value 1 (above) vs Stock Value 2 (below)nd regards,

Brandaris