topic Re: PowerPivot: To display last updated inventory of the month against each day of the month in DAX Commands and Tips

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

Ajayks_11 — Tue, 27 Dec 2022 07:39:52 GMT

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

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

amitchandak — Tue, 27 Dec 2022 08:32:29 GMT

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

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

Ajayks_11 — Tue, 27 Dec 2022 08:46:01 GMT

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

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

amitchandak — Tue, 27 Dec 2022 12:03:37 GMT

Ajayks_11 , if you need last date stock

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

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

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

Ajayks_11 — Tue, 27 Dec 2022 13:44:14 GMT

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

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

Ajayks_11 — Thu, 29 Dec 2022 03:39:16 GMT

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