Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Anonymous
Not applicable

Incorrect totals after using SUMX in combination with DATEADD and blank values

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:

 

Stefan1458_2-1612851807635.png

 

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):

 

Stefan1458_1-1612851666033.png

 

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:

 

Stefan1458_3-1612851937927.png

 

Stefan1458_4-1612851962046.png

 

I did this by creating a measure that adds up all the amounts bought and sold up to the selected year:

 

Changes in inventory =
VAR MAXDate = MAX(Dates[Date])
RETURN
CALCULATE(
SUM(Products[Amount]),
FILTER(
ALLEXCEPT(Products, Products[Product]),
Products[Date] <= MAXDate))
 
I then added a measure for the beginning inventory as the above measure with a DATEADD of -1 years, so that when selecting 2018, it adds everything up to 31-12-2017, for 2019 everything up to 31-12-2018 and so on.
 
Beginning inventory =
CALCULATE([Changes in inventory], DATEADD(Dates[Date].[Date], -1, YEAR))
 
The ending inventory is then calculated as:
 
Ending inventory = [Beginning inventory]+[Amount bought]+[Amount sold]
 
Now, the problem occurs when filtering on the category using a slicer. In that case the total values are incorrect.
 

I tried to solve this by adding a SUMX on the measure:

 

Beginning inventory Totals =
SUMX(VALUES(Products[Product]), [Beginning inventory])
 

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:

Stefan1458_0-1612853486272.png

 

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

1 ACCEPTED 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:

 

v-kelly-msft_0-1613020774195.png

 

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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:

 

v-kelly-msft_0-1613020774195.png

 

 

For the related .pbix file,pls see attached.

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Anonymous
Not applicable

Hi @v-kelly-msft 

 

Thanks for your help! That did it 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.