Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowGet certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started
Dear PowerBI community,
Hope you are doing well. I am trying to build a goods movement model in PowerBI but I have some problems getting the correct numbers to show in my table and I am hoping you can help me out.
I am trying to build a dynamic goods movement model based on data in this format:
Basically, there are some products of some category, during the year an amount is bought and an amount is sold. So for any year, I want to know the inventory at the beginning of that year, the amount bought, the amount sold and the ending inventory. The model works fine as long as there is some form of transaction in a year. In this sample, there is no transaction for product A in 2018. I therefore added a dates table and connected it to the table above (Dates,Date) 1:* (Products, Date):
I have created a table like this, so that when selecting 2017 as the year, the beginning inventory is empty, an amount is bought, an amount is sold and it shows the ending inventory. Then, when selecting the year 2018, the ending inventory of 2017 is shown as the beginning inventory of 2018 and the process repeats:
I did this by creating a measure that adds up all the amounts bought and sold up to the selected year:
I tried to solve this by adding a SUMX on the measure:
This works again fine as long as there is some data, but for the above mentioned example of product A (category Electronics) in 2018 is does not work because there is no data. Below are the two tables, one with the first beginning measure and one with the SUMX:
So, in 2017 a total 500 of product A are bought so that the inventory at the end of 2017 is 500 and that should also be the beginning inventory of 2018, for which there is no data of product A. In my first table I get the 500 correctly as beginning and ending inventory but then the totals are incorrect. After using a SUMX, the totals for the other products are correct but the 500 no longer shows as there is no data in 2018.
I therefore need to find a way to keep the 500 and show the correct total after using the slices on category.
Maybe I am looking in the wrong direction for this, but I have tried several things with the SUMX that have not helped so far. I am really hoping someone can help me with this problem.
Thanks in advance!
Kind regards,
Stefan
Solved! Go to Solution.
Hi @Anonymous ,
Modify the measure [Changes in inventory] as below:
Changes in inventory =
VAR MAXDate = MAXX(ALLSELECTED(Dates),'Dates'[Date])
RETURN
CALCULATE(
SUM(Products[Amount]),
FILTER(
ALLEXCEPT(Products, Products[Product]),
Products[Date] <= MAXDate))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@Anonymous , This should be like
CALCULATE([Changes in inventory], DATEADD(Dates[Date], -1, YEAR))
also, make sure you have all the dates in date table. Dateadd need continuous dates . Means have date -1 year from Min Date
Hi @amitchandak ,
Thanks for your reply. I changed the Dateadd part. The dates table indeed has continuous dates from 01-01-2017 till 31-12-2021. I get the 500 for product A in 2018 correctly with my Beginning inventory measure, but it shows incorrect totals as the table is filtered on category using a slicer. I tried to correct this by using a SUMX, but then the 500 disappears as there is no data in 2018. Do you know how to solve for this?
Thanks in advance for your help.
Kind regards,
Stefan
Hi @Anonymous ,
Modify the measure [Changes in inventory] as below:
Changes in inventory =
VAR MAXDate = MAXX(ALLSELECTED(Dates),'Dates'[Date])
RETURN
CALCULATE(
SUM(Products[Amount]),
FILTER(
ALLEXCEPT(Products, Products[Product]),
Products[Date] <= MAXDate))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |