Running Total With Reset

[FilipeSantos_pt]

I have a problem regarding stock management, the product is a liquid.

At the beginning of the year there is a physical measurement and after that, through the year, is done for example weekly, not mandatory in this period.

This measurement is done because of any loss that might occur between the process of stocking purchased product and sold product.

The intention is after the weekly real measurement enters the running total of the stock should “reset” and ignore previous movements and accumulate the new ones after the date this should be done by store.

For the store I created a dimension to use as a filter in order to show the complete running total of both stores or for each individual one.

Is there any way to do this in DAX or Power Query? I tried to use Power Query with conditional subcategory running total without any luck. Since I am newbie probably did something wrong.

However I found Running totals by subcategory in Power Query by Curbal and this helped me to the subcategory but not with the occasional real time measurement that “resets” everything before that. Also this seems to be very heavy, maybe that’s why she says that we shouldn’t do running totals in Power Query.

What I did at the beginning was the following measure:

Quantity =

CALCULATE(

    SUM( Table1[Amount] ),

    FILTER(

        ALLSELECTED( Dates ),

        Dates[Date] <= MAX( Dates[Date] )

    )

)

and I used the date dimension table and the store dimension table to filter. However this is only global running total without reset

Tried a calculated column that I took from a post in the forum:

Running Total Measure =

VAR _rowDate = table1[Date]

VAR initialDate =

    CALCULATE(

        MAX( table1 [Date] ),

        FILTER(

            table1,

            table1 [Date] < _rowDate

                &&

            table1 [resetCode] = 0

        )

    )

RETURN

IF(

    table1 [resetCode] = 0,

    table1 [Amount],

    SUMX(

        FILTER(

            table1,

            table1 [Date] <= _rowDate

                &&

            table1 [Date] > initialDate

        ),

        table1 [Amount]

    )

)
but didn’t work, it didn't followed the time. On my if statment table1 [Quantity] with a resetCode 0 is the quantity to reset at that moment. Is a new real measurement and after that it should start a new running total treating 00_realQuantity as a initial stock from that moment on.

 

Date	Store	Type of movement	Amount	Running Total	Reset Code
01/01/2020	A	00_initialStock	500	500	0
01/01/2020	B	00_initialStock	300	300	0
01/01/2020	B	sale	-100	200	1
02/01/2020	B	purchase	50	350	2
02/01/2020	B	purchase	40	390	2
03/01/2020	A	sale	-80	420	1
04/01/2020	B	sale	-70	320	1
05/01/2020	A	sale	-20	400	1
06/01/2020	A	purchase	50	450	2
07/01/2020	A	00_realQuantity	350	350	0
07/01/2020	B	00_realQuantity	300	300	0
07/01/2020	A	purchase	50	400	2
08/01/2020	B	purchase	20	320	2

This in Excel would be easier but I am transitioning to Power Bi  and maybe I am experimenting unnecessary steps hope that I can find help.

Thank you in advance.

[FilipeSantos_pt · Accepted solution]

Alberto Ferrari from SQLBi answered this on the following vídeo:

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

[amitchandak]

@FilipeSantos_pt , not very clear. But if it always reset at year start then you can use YTD

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[FilipeSantos_pt]

I know it's confusing. The stock doesn't reset only at the  year start. Through the year new measurements are made and the stock at that moment is corrected to the real one.

The running total at this moment should stop and restart treating this entry 00_realMeasurement as a new initial stock. I corrected the initial post a bit. I hope it's more clear