Implementing running total from arbitrary dates in DAX - Unplugged #34

Create a running total formula in DAX that starts from arbitrary dates instead of considering the complete history available.
Read more about the "unplugged" format: www.sqlbi.com/blog/marco/2021...
#unplugged

[@henrifanda4784]

You always make it so easy. Hats off to you. Watching your DAX magic works is so satisfying. Thanks Alberto.

[@csgexec]

Great job. I’m glad to see you took the time to optimize the calculations into a single measure. 👍🏼

[@JonathanCHANAL]

Hello, this is very useful as always, and I have to say, this is just impressive how it seems so easy for you. I hope I'll have such confidence in a few years. Thank you very much for sharing so many things with us.

[@hoezo]

Great video thank you! I am now trying to apply this logic on resetting the Running Total on a specific time each day.

[@filipedossantos5569]

It's a fantastic job. It's always a pleasure to see a video of someone who really knows the tool they work with. I leave the suggestion to build in DAX the simple linear regression. Currently the suggested DAX formulas only work on an annual or daily level but not correctly on a quarterly or monthly level, at least not when comparing with the results in Excel or maybe is just me that isn't properly applying those solutions.

[@SQLBI]

See this article: sergiomurru.com/2021/06/14/simple-linear-regression-in-dax-with-hierarchy-on-date-table/

[@filipedossantos5569]

@@SQLBI thank you. Really thank you. At least, for me, I think that this is a really important article.

[@esaegess]

Thank you so much for your videos. I've got a different setup and I'm not able to combine actuals and forecast. Actuals are on a daily bases and forecast is on a yearly bases. For my combined actuals and forecast, I've got to a) subtract the total of the actuals from the total forecast (one value for the year) and b) calculate the daily forecast for all days after the latest actual. Would be great if you could get in touch with me or if you could create another great video ;-). Thank you!

[@JohnSullivan1]

Fantastic. Thank you Alberto!

[@alexsandersilva7402]

Amazing. Thank you.

[@caleboud]

Thanks for this video , as usual very clearly explained. I was just wondering if I could adapt your code and use it to do a running total between dates. Suppose you have 2 slicers where you can select a start date and an end date…

[@EST1865]

Your DAX is immaculate

[@johansantacruz6464]

I would like to get this performance improvements in my measures. Thanks Alberto!

[@user-sb5vf7qq1e]

SQLBI Never let you down!!! Thanks for your video. I have a thought, if the interval between each reset date is fixed, is it a good idea to set up a calculated column in the Date table to show the Last Reset date so that we can apply Datesbetween function always horizontally in Dates table directly. It will save somehow the vertical scan resources, right?

[@pbihari0214]

Thank you

[@AB-nx5rl]

Hi Alberto. Would it be possible for you to post a similar video on SQLBI or here in which you demonstrate how the running total "curve" gets adjusted to a start date and end date that are selected via a date slicer. Basically I select a time period on a slicer and the cumulative curve starts from 0 in correspondence of the min date selected via the slicer and the max date is the max date of the selection.

[@todortodorov9472]

Even your video is complex for my knowledges, explanation makes it easy. It's very useful for me, thanks Alberto! I'm trying to create more complex logic for resetting - two (for now) or (even) more triggers. For example: Town | Type | SubType | Qty | RT Qty 1 | 1 | 1 | 5 | 5 1 | 1 | 2 | 4 | 9 1 | 1 | 1 | 2 | 11 1 | 1 | 2 | 3 | 14 1 | 1 | 3 | 6 | 20 2 | 1 | 1 | 1 | 1 2 | 1 | 1 | 7 | 8 2 | 1 | 2 | 3 | 11 2 | 1 | 1 | 5 | 16 2 | 1 | 2 | 6 | 22 2 | 1 | 3 | 8 | 8 2 | 1 | 4 | 14 | 22 2 | 1 | 5 | 2 | 2 2 | 2 | 1 | 4 | 6 2 | 2 | 2 | 8 | 14 3 | 2 | 2 | 2 | 16 3 | 2 | 3 | 3 | 19 In my table I have no dates, information is sorted with index column. Triggers: 1. If Town is changed in case that Type is 1. RT Quantity defenetelly need to be reset, even if there is enough space. In example, row with Qty = 1 need to be in different RT Qty than first 5 rows. 2. If Type is not 1, even on changes of town, RT Quantity need to be reset when reach maximum level, in example 22. Tried to make measure [Reset] more complex (with logic above). Reaching trigger row (LastReset), RT is reset, but on next row RT is not correct and calculate RT from first row of the table, not from LastReset. Noticed that using RowsToUse variable instead of DatesToUse, need to filter table from LastReset to current row. Used FILTER(; [Index] >= LastReset && [Index]

[@dankotsmith9459]

I have a OrderFactTable that has an OrderDate. I need to calculate a RunningTotal on a Patient Level for 45 days when a certain trigger happens on that patient. In this instance the Trigger would be when it has been over 90 days since their previous order. Any thoughts on that?

[@nishantkumar9570]

Thank you very much. Could you please simulate the FIFO concept of inventory using DAX. I love the way you explain things so beautifully and easily. You make every concepts very clear. Thank you very much.

[@SQLBI]

FIFO requires recursion, which is not available in DAX. There are workarounds but we didn't have time to work on that extensively yet. It's in our backlog, but no date yet.

[@chrismettler136]

@@SQLBI excellent video as always. A FIFO sample would be wonderful :-)

[@nishantkumar9570]

@@SQLBI thank you very much.... I guess all the 'X' functions un DAX is iterative or recursive... I'll eagerly be waiting for the video. Thank you once again.

[@SQLBI]

X functions are iterators (see them on dax.guide). DAX does not have recursion.

[@aniruddhmathur3535]

If date is same ..and running total is calculated as per group by then how I can apply DAX

[@christinelowery2415]

What if I need the ref date to be the selected date plus 28 days? How would I write that?

[@christinelowery2415]

Every week needs to be the total of 4 weeks (itself plus next3). It rests each week

[@Wolfangs88]

Is there a DAX formula for running total since the beginning of the year ?

[@SQLBI]

Look for year-to-date calculations. www.daxpatterns.com/standard-time-related-calculations/

[@mdnaiyaz9177]

How to find running total of last 5 days, Date value answer 1. 10. 10 2. 20. 30 3. 30. 60 4. 40. 100. 5. 50. 150 6. 60. 60 7. 70. 130 8. 80. 210 9. 90. 300 10. 100 400 15. 50. 50 16. 30. 80 17. 20. 100 Need DAX for Answer column