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
vincentpit
Frequent Visitor

Rolling Calculation

Hello Guys.

 

Im trying to figure out how to use a rolling amount calculation. I tried the following formula:

 

Rolling_up = CALCULATE(SUM('excel-planning production_progress'[boxes_done]);FILTER(ALL('excel-planning production_progress'[last_change].[Date]);'excel-planning production_progress'[last_change].[Date]<=MAX('excel-planning production_progress'[last_change].[Date])))

 

Instead of getting the cumulative value i'm getting a copy of the [boxes_done] value. voorbeeldforum.PNG

The rolling up value should be: 12, 56, 67, 100 .... etc.

 

Thanks in advance for your help!

 

Vincent Pit

 

2 ACCEPTED SOLUTIONS

Hi Smoupre, thanks for the quick response!

 

I've tried quick measure, which gives me the following formula:

cumulative =
CALCULATE(
SUM('excel-planning production_progress'[boxes_done]);
FILTER(
ALLSELECTED('excel-planning production_progress'[last_change]);
ISONORAFTER('excel-planning production_progress'[last_change]; MAX('excel-planning production_progress'[last_change]); DESC)
)
)

 

This formula provides me with the same results (problems) as described before.

 

Thanks

View solution in original post

Greg_Deckler
Super User
Super User

When I recreated this, I got the correct results.

 

boxes_done running total in last_change = 
CALCULATE(
SUM('Boxes'[boxes_done]),
FILTER(
ALLSELECTED('Boxes'[last_change]),
ISONORAFTER('Boxes'[last_change], MAX('Boxes'[last_change]), DESC)
)
)

Here is my Enter Data query, make sure that your last_change is a Date/Time column and not text or something.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdDRCcAgDATQVUq+C+aS2KoTdAdx/zWqFhT60QpHkMDj1JwJgU2Edrr0OMWhHeGkhhE2aPKaWNtSYp9U9j8l6CoOZbaiehd4duFLyaP8q0p1AbVnxSQ2LxhWlCWEmqHqX5Qb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ordernr = _t, articlenr = _t, last_change = _t, boxes_amount = _t, boxes_done = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ordernr", Int64.Type}, {"articlenr", type text}, {"last_change", type datetime}, {"boxes_amount", Int64.Type}, {"boxes_done", Int64.Type}})
in
    #"Changed Type"

 

 


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

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

When I recreated this, I got the correct results.

 

boxes_done running total in last_change = 
CALCULATE(
SUM('Boxes'[boxes_done]),
FILTER(
ALLSELECTED('Boxes'[last_change]),
ISONORAFTER('Boxes'[last_change], MAX('Boxes'[last_change]), DESC)
)
)

Here is my Enter Data query, make sure that your last_change is a Date/Time column and not text or something.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdDRCcAgDATQVUq+C+aS2KoTdAdx/zWqFhT60QpHkMDj1JwJgU2Edrr0OMWhHeGkhhE2aPKaWNtSYp9U9j8l6CoOZbaiehd4duFLyaP8q0p1AbVnxSQ2LxhWlCWEmqHqX5Qb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ordernr = _t, articlenr = _t, last_change = _t, boxes_amount = _t, boxes_done = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ordernr", Int64.Type}, {"articlenr", type text}, {"last_change", type datetime}, {"boxes_amount", Int64.Type}, {"boxes_done", Int64.Type}})
in
    #"Changed Type"

 

 


@ 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...
Greg_Deckler
Super User
Super User

Have you tried the Running Total quick measure?


@ 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 Smoupre, thanks for the quick response!

 

I've tried quick measure, which gives me the following formula:

cumulative =
CALCULATE(
SUM('excel-planning production_progress'[boxes_done]);
FILTER(
ALLSELECTED('excel-planning production_progress'[last_change]);
ISONORAFTER('excel-planning production_progress'[last_change]; MAX('excel-planning production_progress'[last_change]); DESC)
)
)

 

This formula provides me with the same results (problems) as described before.

 

Thanks

Hi Smoupre,

The measure works, thanks 🙂 . Only now I encounter another problem.
I have to filter on ordernumber, if I unfilter, it takes forever to load the visual. If I delete the measure everything works fine. Do you have any tips that we can use in order to make the visual work smoothly?

I read something about a poorly written measure, but since we used quick measure I think that would be odd.

Sorry for the inconvenience.

Vincent

 

Well, how many records are we talking about here? The only function that I can think of that could be slowing things down would be ISONORAFTER, the other functions are pretty standard and we aren't doing anything like a MAXX. But, that particular function might be causing some issues potentially.

 

 


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

 

Sorry for the late response,

 

The database which I use for the running total consists of 2600 rows. The amount of rows will grow each day by lets say 80 to 100 units.

So the database itself is not that big right now. Is there any way to bypass the ISONORAFTER?

 

 

 

Maybe a simple <= filter?


@ 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 smoupre,

 

I still can't get it right. The formula works with a small amount of rows but in this case it keeps loading and loading at the moment i put the measure 'Rolling total' in.  This is also the case when i filter at a certain 'Ordernr', while there are less rows.

 

I uploaded a test file to dropbox so you can see the problem.

 

https://www.dropbox.com/s/1jht6epoaqhc91x/Track%20%26%20Trace%20Test.pbix?dl=0

 

The rolling total value i try to determine is 'Dozen klaar' by 'Ordernr' (sorry, dutch terminology at the headers).

 

Thanks in advance!

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.