Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Ajayks_11
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

 

 

Thank you all in advance

 

5 REPLIES 5
amitchandak
Super User
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

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

@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

amitchandak_0-1672142550727.png

 

 

Row you can have

 

amitchandak_1-1672142567059.png

 

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...

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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