cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

5 REPLIES 5
Super User

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

Frequent Visitor

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

Super User

@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

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

Frequent Visitor

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

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors