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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dswinden
Helper I
Helper I

Rolling Sum Not Working when Empty Data

Hello!  I have used this formula in dozens of examples but for whatever reason it is not working for this example.

 

I will try to explain this as best possible.  I have a data source that I receive that has a date column called "Last Seen On".  This is a count of inventory table, so if a product was last in stock on December 10th, thats the date that that will be in "Last Seen On".

 

From there, I connected that date to a date table so that i can quickly filter by month, quarter, year, etc.

So here is my dilemma.  I want to measure the rolling 2 months inventory of a SKU.  So using the above example, a product was last seen in inventory on December 10th.  If i use January 2023 as my filter, then i should see the inventory of December 2022 and January 2023.  Example below.

dswinden_2-1674691395225.png

 

The measure i am using (and have used dozens of time before) is ;

 
CALCULATE(sum('Distribution Listings'[Current stock]),DATESINPERIOD('Date XREF'[Month Start],LASTDATE('Date XREF'[Month Start]),-2,MONTH))
 
BUT the result i get when i use this measure is below.  Because there is only two records for this item, and they occured in November and December, i only see the R2M results of Nov/Dec.  I SHOULD see January 2023 showing 9 in current stock and then Feb 2023 showing 0 (as it does with the "Expected Result" column above
dswinden_1-1674691288424.png

 

 

 

8 REPLIES 8
Padycosmos
Solution Sage
Solution Sage

Thank you, but the same outcome as previous two methods.  My best guess is that there is either a problem with my date hierarchy, or that it is having a challenge handling data with no records (There is no January records of this dataset, so when i select january for R2M, it shows null)

In your case, the total is calculated based only on the current value and the immediately preceding value.So in Feb 22, it is adding The value of Feb 22 and Jan 22, which are both 0 and therefore you get 0 as the result

Hi Pady, i show in my example above.  I am trying to pull Jan 2023 rolling 2 months inventory.

December 2022 had 9 units
January 2023 has 0 units

R2M result should be 9.    I have now solved my challenge, somehow my date table had duplicates so the relationship created was incorrectly created as Many to Many.  Solved by removing duplicates and changing to Many to One

Greg_Deckler
Super User
Super User

@dswinden Better Rolling Average - Microsoft Power BI Community


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler , thanks for sharing but i am looking for the total sales during that time, not just the average.  Would your variable still work for that?

@dswinden Sure, just use SUMX instead of AVERAGEX, regardless the rolling part is the same.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks for the help!  I attempted this modifying for my fields (below) but do not get the result i need.

 

R2M Test =
    VAR __EndDate = MAX('Distribution Listings'[Last Seen])
    VAR __2MonthsAgo = EOMONTH(__EndDate, -2)
    VAR __StartDate = DATE(YEAR(__2MonthsAgo), MONTH(__2MonthsAgo), 1)
    VAR __Table =
        SUMMARIZE(
            FILTER(ALL('Date XREF'),'Date XREF'[Month Start]>=__StartDate && 'Date XREF'[Month Start]<=__EndDate),
            'Date XREF'[Month Start],
            "__Value",SUM('Distribution Listings'[Current stock])
        )
RETURN
    SUMX(__Table,[__Value])
 
the resulting data, is the same as using the formula i provided in my original post but will not show me the rolling inventory for future months.  The outcome i need is that for January 2023, the Rolling 2 month inventory is January + December.  Since there is 0 inventory in January and 9 in December, the result should be 9, but both the "DATESINPERIOD" approach above and your variable return the same result.  Any ideas?
 
dswinden_1-1674748550468.png

 


 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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