PowerPivot: To display last updated inventory of the month against each day of the month

[Ajayks_11]

Hi All,

I am totally new to the PBI community and have been dealing with simple functions till now. Now I need help related to my inventory data

I have a product data base in which the inventory(total Stock) is updated on any random day, 3-4 times a month. I need to use the last updated inventory of a month as the reference inventory for each day of the month reflecting in a power pivot table (like MAtrix in BI). I expand/collapse the columns of pivot  basis on Date table(Year/Quarter/Month/Week/Day)

This is how the fact table looks like:

Date        Brand Product Sales Inventory

1-12-22     X        A            1  

1-12-22     X        B             2

1-12-22     Y        D             6

2-12-22      X        A             2          10

2-12-22      X        B             2           20

2-12-22      X         C                         30

2-12-22      Y        D                          15

3-12-22     X        A            1  

3-12-22     X        B             2

3-12-22     X        C             4

.

.

.

20-12-22      X        A             2         15

20-12-22      X        B             2          20

20-12-22      X         C                        20

20-12-22      Y        D                         20

My output Matrix table should look like: (With only Stock Measure)

Brand     Product      Date

                                1-12-2022                   2-12-2022   ......

+X                                55                                   55

Y                 D              20                                   20

Further expandable/Collapsable:

Brand     Product      Date

                                +Dec

X                  A              15                                 

                    B              20                                 

                    C              20                                 

Y                  D              20                                 

Eagerly waiting for your input in creating the total Stock measure 

Have been trying measures like:

= calculate(sum(table[Total Stock]), LASTNONBLANK(table[Date], sum(table[Total Stock]))

This measure returns correctly in a week(approx for one data upload), but remains blank in either Month or Day level

Thank you all in advance

[amitchandak]

@Ajayks_11 , You can get the one without + button in Matrix. I doubt the second one. You can selectively open + button for rows

[Ajayks_11]

Thanks @amitchandak  for the fast response. 

I am not able to understand what you meant. If you are saying that only row expansion is possible and I will not be able to get product level stock details, then atleast give that solution for now. Also, will have to do the same for sameperiod last year stock (another measure), which I forgot to mention

Thanks again

[amitchandak]

@Ajayks_11 , if you need last date stock

calculate(LASTNONBLANK(table[Date], sum(table[Total Stock]))

or

calculate(LASTNONBLANK(table[Date], sum(table[Total Stock])), filter(all(Table[Month Year]) , table[Month Year] = max(Table[Month year]) ) )

perfer date table

calculate(LASTNONBLANK(table[Date], sum(table[Total Stock])), filter(all(Date) , Date[Month Year] = max(Date[Month year]) ) )

There is not + button for column

Row you can have

Power BI closingbalancemonth closingbalancequarter closingbalanceyear, lastnonblankvalue- Closing Balance, Distibutre Target: https://youtu.be/yPQ9UV37LOU

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[Ajayks_11]

Hi All, @amitchandak 

calculate(sum(table[Total Stock]),LASTNONBLANK( PARALLELPERIOD(table[Date],0,MONTH),sum(table[Total Stock]))) is supposed to work in PowerBI it seems, but somehow it is not working in PowerPivot. Can you help? Seems Powerpivot takes input of LASTNONBLANK as column only

[Ajayks_11]

Hi @amitchandak 

Using LASTNONBLANK(table[Date] after Calculate is giving me a Date as an output. That too it's giving me any output, on the day, when the Stock is updated. Else returning blanks

And for the +button and all, please ignore those doubts of mine. I put + in Columns, only because I use PowerPivot and not PowerBI and there it is Expanded like Rows

Please suggest something else as I need stock value showing in all days, the value that is last updated in that month

Thanks for your response