cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Cumulative reset after reaching sales target

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 +

+

4 REPLIES 4
Super User

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!

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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!

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

New Member

Thanks @rajendraongole1!

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

Super User

@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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.