topic Cumulative reset after reaching sales target in DAX Commands and Tips

Cumulative reset after reaching sales target

SailorMoon98 — Sat, 20 Jul 2024 19:09:22 GMT

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 +

Re: Cumulative reset after reaching sales target

rajendraongole1 — Sun, 21 Jul 2024 13:59:18 GMT

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!!

Re: Cumulative reset after reaching sales target

Greg_Deckler — Tue, 23 Jul 2024 20:41:38 GMT

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.

Re: Cumulative reset after reaching sales target

rajendraongole1 — Wed, 24 Jul 2024 06:44:02 GMT

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!!

Re: Cumulative reset after reaching sales target

SailorMoon98 — Mon, 29 Jul 2024 19:01:23 GMT

Thanks rajendraongole1!

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