Optimizing running total with a reset measure

[Anonymous]

Hello

I am currently using a running total that resets every time the measure [Pos to Neg] = 1. The running totals is just counting the dates/days.

The measure works fine, however it is too slow to actually be useful in production. The fact table contains about 1.4 million rows.

I am using most of the pattern from this SQLBI video: Implementing running total from arbitrary dates in DAX - Unplugged #34 - YouTube

RT With Reset = 

VAR _RefDate =
            MAX ( 'Calendar (Period)'[Period Date] )
        VAR _AllNegativeDates =
            FILTER ( ALL ( 'Calendar (Period)'[Period Date] ), [No of Days (Internal)] = 1 )
        VAR _NegativeDatesBeforeNow =
            FILTER ( _AllNegativeDates, 'Calendar (Period)'[Period Date] <= _RefDate )
        VAR _LastNegative =
            MAXX ( _NegativeDatesBeforeNow, 'Calendar (Period)'[Period Date] )
        VAR _AllResetDates =
            FILTER ( ALL ( 'Calendar (Period)'[Period Date] ), [Pos to Neg] = 1 )
        VAR _ResetDatesBeforeNow =
            FILTER ( _AllResetDates, 'Calendar (Period)'[Period Date] <= _RefDate )
        VAR _LastReset =
            MAXX ( _ResetDatesBeforeNow, 'Calendar (Period)'[Period Date] )
        VAR _DatesToUse =
            DATESBETWEEN ( 'Calendar (Period)'[Period Date], _LastReset, _LastNegative )
        VAR _Result =
            COUNTROWS ( _DatesToUse )
        VAR _LastDateWithQty =
            MAX ( 'Calendar (Period)'[Period Date] )
        VAR _FirstVisibleDate =
            MIN ( 'Calendar (Period)'[Period Date] )
            
        RETURN
            IF (
                _FirstVisibleDate <= _LastDateWithQty
                    && [No of Days (Internal)] <> BLANK(),
                _Result
            )

 The DAX query takes around 40 secs to run, with a filter on one specific product. If no product is filtered, it will likely go on for a lot of time.
In the final report, all products should be visible, which is why this is an issue.

I believe my issue with the measure is that I have so many steps related to the date, but I am not sure how I could optimize it. The other measures that are referenced in the measure is now slow.

This is how it looks in a matrix table visualization for reference

Date	RT With Reset	Pos to neg
04-03-2022		1
05-03-2022	1
06-03-2022	2
07-03-2022	3
08-03-2022		1
09-03-2022	1

[OwenAuger · Accepted solution]

Hi @Anonymous 

I happened to come across your post. I have had a play around and have attached a PBIX with some potential improvements.

The main things I looked at:

1. Minimise nested measures (some overhead involved):
   • Rewrote [Qty on Hand Total] using SUMX.
   • Eliminated [Pos to Neg] and [No of Days (Internal)] from the final measure.
2. Rather than using ALL ( 'Calendar'[Period Date] ), use a smaller date range covering global min/max Stock[Date].
3. Rewrote the logic a bit to detect when the count should start. See AllRequiredDatesFlag variable.
4. Rather than counting dates in a table containing a contiguous range of dates as in original _DatesToUse
   variable, take the difference between date bounds & add 1.

A matrix with just Period Date & Working (IMPROVED) Measure now takes a bit under 2 seconds for me, or a bit over 3 seconds with Qty Projected as well.

The relevant updated measures are:

Qty On Hand Total = 
SUMX (
    Stock,
    Stock[Qty On Hand] + Stock[Qty Supply] - Stock[Qty Demand]
)

Qty Projected = 
VAR MinStockDate =
    CALCULATE ( MIN ( Stock[Date] ), REMOVEFILTERS ( ) )
RETURN
    CALCULATE ( 
        [Qty On Hand Total],
        DATESBETWEEN ( 'Calendar'[Period Date], MinStockDate, MAX ( 'Calendar'[Period Date] ) )
    )

Working (IMPROVED) Measure = 
VAR _RefDate = MAX ( Calendar[Period Date] )
VAR MinStockDate =
    CALCULATE ( MIN ( Stock[Date] ), REMOVEFILTERS ( ) )
VAR MaxStockDate =
    CALCULATE ( MAX ( Stock[Date] ), REMOVEFILTERS ( ) )
VAR AllRequiredDates =
    DATESBETWEEN (
        Calendar[Period Date],
        MinStockDate,
        MaxStockDate
    )
VAR AllRequiredDatesFlag =
    ADDCOLUMNS ( AllRequiredDates, "@Pos", [Qty Projected] >= 0 )
VAR RelevantDatesFlag =
    FILTER (
        AllRequiredDatesFlag,
        Calendar[Period Date] <= _RefDate
    )
VAR LastPositive =
    MAXX (
        FILTER ( RelevantDatesFlag, [@Pos] ),
        Calendar[Period Date]
    )
VAR LastNegative =
    MAXX (
        FILTER ( RelevantDatesFlag, NOT [@Pos] ),
        Calendar[Period Date]
    )
RETURN
    IF (
        LastNegative = _RefDate,
        INT ( LastNegative - LastPositive )
    )

There's bound to be room for improvement, but hopefully this is of some use.

Regards,

Owen

[Anonymous]

Hi OwenAuger and thank you for taking the time.

You certainly optimized it a lot, and it works as intended. Whether it's fast enough for production, I will have to analyze further. Before marking this as a solution I am exploring the table mentioned by johnt75. Although I believe this should be marked as the solution, even if the table functions better in production, since a measure was my original question.

[Mafesa_Sasol]

Hi @Anonymous @johnt75 @OwenAuger I have been trying to follow the thread and looks like could be of assistance to my challenge. As can be seen from the attached snapshot. I am trying to develop a calculated column that does a running total for every specific employee using the date between standby column. That column is the Consecutive Employee Check. Please ignore the first value (11). However, I need it to reset after each value on the Days Between Standby Column that is greater than 1. For example, I need to reset 46, 22 and 8. However, the running total continues throughout which is not what I want. Your assistance would be highly appreciated. This is the sample for each unique ID and resets after each new ID but running total continues through for each and every unique ID and does not reset after each new value in the Days Between Standby greater than 1.
This is the DAX for this column:

Consecutive Employee Check =

VAR CurrentStandbyDays = LOOKUPVALUE(Standby[Standby Days], Standby[Index], Standby[Index])

VAR ConsecStandbyDays =

IF(

LOOKUPVALUE(Standby[Personnel Number], Standby[Index], Standby[Index]) = LOOKUPVALUE(Standby[Personnel Number], Standby[Index], Standby[Index]+1)

&& LOOKUPVALUE(Standby[Days Between Standby], Standby[Index], Standby[Index]) < 7 ,

CALCULATE (

    SUM ( 'Standby'[Standby Days] ),

FILTER ( ALLEXCEPT(Standby,'Standby'[Personnel Number], 'Standby'[Days Between Standby]), 'Standby'[Index] <= EARLIER ('Standby'[Index]))

)

,

CurrentStandbyDays

)

Return ConsecStandbyDays

[johnt75]

I've no idea if this will be more efficient, but you could try something like

Running Total with reset =
var currentDate = SELECTEDVALUE('Calendar'[Date])
var summaryTable = ADDCOLUMNS( 
   SUMMARIZE( FILTER('Calendar', 'Calendar'[Date] < currentDate ), 'Calendar'[Date]), "@pos to neg", [Pos to Neg] )
var lastReset = SELECTCOLUMNS( TOPN(1, FILTER( summaryTable, [@pos to neg] = 1 ), [Date], ASC ),
    "@last date", [Date] )
return COUNTROWS( DATESBETWEEN( 'Calendar'[Date], lastReset, currentDate ) )

[Anonymous]

Hello and thank you for taking interest in my question.

Sadly this does not work. It calculates a running total, but does not reset. The lastReset variables does not return anything.

[johnt75]

could you share a small sample of data, ideally in a pbix file ?

[Anonymous]

Hello

I have now created a sample pbix file with the data and a related calendar. The measure this post regards is called "Working (SLOW) Measure". I have visualized the matrix on page 1 as shown here

This sample data only consists of one product (part 1713622). I have recreated the measures necessary for the calculation.

Does anyone know how I can possibly improve the performance of this measure?

When using the 'performance analyzer' in this sample file, it takes 20 seconds to refresh the matrix.


Some more information for those who wants to try:
No of days (internal) is = 1 if Qty Projected is < 0
'Pos to neg' = 1 if qty projected went from a positive number to a negative number
The 'Working (SLOW) Measure' is a running total of 'No of days (Internal)' that resets every time that 'Pos to Neg' = 1.

Thank you in advance for those willing to give it a shot!
Sample .pbix file uploaded to wetransfer.com: https://we.tl/t-mf6rT0A9Ou

[johnt75]

Try creating a summary table as a calculated table. That will obviously lead to increased data refresh time, but it will make populating report visuals pretty much instantaneous.

I created a table as

Summary Table = SUMMARIZECOLUMNS( 'Calendar'[Period Date], "@Qty Projected", [Qty Projected] )

Then added columns as

@Pos to Neg = 
var currentDate = 'Summary Table'[Period Date]
var currentValue = 'Summary Table'[@Qty Projected]
var prevValue = LOOKUPVALUE( 'Summary Table'[@Qty Projected], 'Summary Table'[Period Date], currentDate -1 )
return IF( currentValue < 0 && prevValue > 0, 1 )

Running Total = 
var currentDate = 'Summary Table'[Period Date]
var lastResetDate = SELECTCOLUMNS( TOPN( 1, FILTER( ALL('Summary Table'), 'Summary Table'[@Pos to Neg] =1 && 'Summary Table'[Period Date] < currentDate), 'Summary Table'[Period Date]),
"@last date", [Period Date])
return DATEDIFF( lastResetDate, currentDate, DAY )

The problem with doing it all in measures is that it has to calculate the quantity twice for each date, once for today and once for yesterday, and each calculation is having to iterate over all the previous rows.

[Anonymous]

Hi and thanks again.

In the production model there are multiple part ids, how would I incorporate this? There can also be several dates between entries, as you can see in the sample model. (how does this affect your CurrentDate -1?

[johnt75]

To cater for multiple parts you can amend the code to the below.

Summary Table = SUMMARIZECOLUMNS( Stock[Part_Id], 'Calendar'[Period Date], 
   "@Qty Projected", [Qty Projected] )

@Pos to Neg = 
var currentDate = 'Summary Table'[Period Date]
var currentPartID = 'Summary Table'[Part_Id]
var currentValue = 'Summary Table'[@Qty Projected]
var prevValue = LOOKUPVALUE( 'Summary Table'[@Qty Projected], 
    'Summary Table'[Period Date], currentDate -1,'Summary Table'[Part_Id], currentPartID )
return IF( currentValue < 0 && prevValue > 0, 1 )

Running Total = 
var currentDate = 'Summary Table'[Period Date]
var currentPartID = 'Summary Table'[Part_Id]
var lastResetDate = SELECTCOLUMNS( TOPN( 1, 
FILTER( ALL('Summary Table'), 
'Summary Table'[@Pos to Neg] =1 && 'Summary Table'[Period Date] < currentDate
&& 'Summary Table'[Part_Id] = currentPartID
), 'Summary Table'[Period Date]),
"@last date", [Period Date])
return DATEDIFF( lastResetDate, currentDate, DAY )

[Anonymous]

Hi again.

I believe this almost works - I think the problem is with the CurrentDate -1 in the "pos to neg". If you look at this screenshot, you can see that it did not register the change from pos to neg. I believe this is because there is more than one date between the 2 rows:

My green 1 suggests that there should have been a "1" here. This means that the rolling total is not correct, sadly.

[johnt75]

Try replacing the prevValue definition with

var prevValue = SELECTCOLUMNS( TOPN( 1, 
FILTER( ALL( 'Summary Table'), 'Summary Table'[Part_Id] = currentPartID
&& 'Summary Table'[Period Date] < currentDate ),
'Summary Table'[Period Date], DESC
),
"@value", 'Summary Table'[Qty Projected]
)

[Anonymous]

Hi and thanks again, 

You are certainly getting closer. However, the running total is only supposed to count when qty projected is negative, otherwise it should just be blank. You can see this in the sample pbix file aswell.

I have attached a photo that shows where it should be blank.

[johnt75]

Add the currentValue variable definition from Pos to Neg to the Running Total as well then change the return statement to be

return IF( currentValue < 0, DATEDIFF( lastResetDate, currentDate, DAY ) )

[Anonymous]

Hi again

This seems to work. I do have one follow-up question before closing this. I guess I did not mention this, so my mistake.

If you take a look at the picture below, you can see that the running total stops at the 17-06-2022, however in reality the running total should stop at 20-06-2022, since the days between these are also 'stock out' periods. Is this do-able? 

[johnt75]

do you have a calendar table rather than just the dates on your data table? If not you should create one, as the summary table was designed to get the dates from a full calendar table, which would mean that all dates would be included in it rather than just those dates for which your data table has entries.

[Anonymous]

I do have one, yes. My issue is that it takes forever to use my calendar table.
Currently running the below DAX since 10 minutes and counting.. This is why I used the other date. (this took 1 min with the other date)

[Anonymous]

It finished. It returns approx. 28 million rows instead of 1 million. So I do not believe that this is worth the trade-off.

EDIT: And I run out of memory when creating one of the columns..

[johnt75]

the only other thing I can suggest is maybe limiting the number of rows you import during development and only having the full data set when its published in the service.

maybe you could do something with parameters? you could have a power query parameter set to "dev" when you're working in the desktop and set it to "live" in the service. your query could check the value of that parameter and only pull in a limited number of parts or something if its set to "dev".