Cumulative reset after reaching sales target

[SailorMoon98]

Hello everyone, 

I have sales data set containing sales amount of every stores in 3 different regions. Each region has their own sales target (column Target Sales), I want to calculate a cumulative sum of sales amount such that it is resetted once the target is reached or almost reached. 

I tried using grouping method but do not get 100% desired result. 

For example, for the South region, the 'Reset cumulative' works perfectly until group 3.

Group 1 includes 0.29 + 8.57 +

+

[rajendraongole1]

Hi @Greg_Deckler - Thanks for sharing, I have tried another approach to get the expected result.

@SailorMoon98 - Please check use the below measure for cummulative reset:

Reset Cumulative Sales2 =

VAR CurrentDate = MAX('Sales'[Date])

VAR CurrentRegion = MAX('Sales'[Region])

VAR TargetSales = MAX('Sales'[Target Sales])

VAR RunningTotalTable =

    FILTER(

        ALL('Sales'),

        'Sales'[Region] = CurrentRegion &&

        'Sales'[Date] <= CurrentDate

    )

VAR RunningTotal =

    SUMX(RunningTotalTable, 'Sales'[Sales Amount])

VAR PreviousSalesTable =

    FILTER(

        ALL('Sales'),

        'Sales'[Region] = CurrentRegion &&

        'Sales'[Date] < CurrentDate

    )

VAR PreviousSales =

    SUMX(PreviousSalesTable, 'Sales'[Sales Amount])

VAR ResetRunningTotal =

    IF(PreviousSales >= TargetSales,

        RunningTotal - PreviousSales,

        RunningTotal

    )

RETURN

ResetRunningTotal

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[rajendraongole1]

Hi @SailorMoon98 -create a measure to calcalte cumulative sales amount

Cumulative Sales =
CALCULATE(
SUM(Sales[Sales Amount]),
FILTER(
ALLSELECTED(Sales),
Sales[Date] <= MAX(Sales[Date])
)
)

Another measure  that will reset the cumulative sales amount when the target is reached.

Reset Cumulative Sales =
VAR CurrentRegion = MAX(Sales[Region])
VAR CurrentTarget = MAX(Sales[Target Sales])
VAR SalesBeforeCurrentDate =
FILTER(
ALL(Sales),
Sales[Region] = CurrentRegion &&
Sales[Date] <= EARLIER(Sales[Date])
)
VAR CumulativeSalesUntilNow =
SUMX(
SalesBeforeCurrentDate,
Sales[Sales Amount]
)
VAR Groups =
GENERATE(
SalesBeforeCurrentDate,
VAR GroupIndex =
SUMX(
FILTER(
SalesBeforeCurrentDate,
Sales[Date] <= EARLIER(Sales[Date])
),
Sales[Sales Amount]
) / CurrentTarget
RETURN
ROW("GroupIndex", CEILING(GroupIndex, 1))
)
VAR CurrentGroup =
MAXX(
FILTER(
Groups,
Sales[Date] = EARLIER(Sales[Date])
),
[GroupIndex]
)
RETURN
IF(
CumulativeSalesUntilNow - (CurrentGroup - 1) * CurrentTarget < CurrentTarget,
CumulativeSalesUntilNow - (CurrentGroup - 1) * CurrentTarget,
0
)

Hope it helps.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[SailorMoon98]

Thanks @rajendraongole1!

I tried yourn second suggesstion but it still does not work. 

[Greg_Deckler]

@rajendraongole1 Tried to implement this on a test file. Was getting an error using EARLIER ( no previous row context ). So, tried to modify it but not working. See attached PBIX file below signature.