cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
corange Helper IV
Helper IV

Cumulative Reset after a value is reached

Hi everyone,

 

I am trying to build up a tracking systems to manage our client. So far I have calculated a cumulative column that reset each FY. Now I can see the cumulative number of jobs for each month. For every 150 jobs, the clients received a gift.

 

I would like to do few things: How do I group then into bin of 150 with the cumultive resetting for the next month when I have reached the 150 mark?

 

Is there a way in BI to mark the event where there reach their target?

 

I would like for the column to display TICKET when the 150s is reached in another column next to the month in consideration

 

The left over after 150 needs to be distributed to the following month until we reached again the 150 and it needs to reset again This needs to happen by clients I hope someone can really healp here.

 

Thanks team.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Cumulative Reset after a value is reached

Please find the link :

https://www.dropbox.com/s/vjeaoyqqnqhwtao/Carry.pbix?dl=0

 

I am getting 56 in Aug. So not able to get how you got 126.





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


View solution in original post

15 REPLIES 15
Super User IV
Super User IV

Re: Cumulative Reset after a value is reached

Try to use MOD

https://docs.microsoft.com/en-us/dax/mod-function-dax

mod([Cumm Total],150)

 

https://docs.microsoft.com/en-us/dax/quotient-function-dax

quotient([Cumm Total],150)

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


corange Helper IV
Helper IV

Re: Cumulative Reset after a value is reached

Hi,

 

Thanks for your reply. 

 

It doesnt really work for me. Because each month there is a value and I dont necessary reach the 150 bookings. 

 

This is how the table presents: 

Capture.PNG

On the line highlighted in yellow, this is where we should be giving a prize, then the 7 bookings is carried to the next month and added to the new bookings for that month. If it is was a round 150 then it will reset at 0. I will also like to be able to track when we have given the prize or when we should with maybe conditional formatting? 

 

This is what I have got in my head but not even sure we can actually do this on BI. 

 

Super User IV
Super User IV

Re: Cumulative Reset after a value is reached

When you take Mod means remainder, then it will show you 7 at the last in feb.

 

The quotient should tell no of price





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


corange Helper IV
Helper IV

Re: Cumulative Reset after a value is reached

Hi, 

 

Thank you again. I did understand your explanation and i believe I was unclear on my answer. 

 

Capture.PNG

  • 1st Table is my number of bookings for each month. 
  • Table 2 is using your formula MOD which give me the reminderafter I have reached 150
  • Table 3 is the number of prizes. 

I would like the carry over to be integrated so it gives me the right number of prize to give for the month. 

 

In the example I have highlighted: 

 

203 /150 = 1 prize

(368 + 53) / 150 = 2 Tickets - carry over is 121 bookings 

(289 + 121) / 150 = 2 prizes

I should be giving 2 prizes in Sept not 1 as the carry over means that we have reached 150 bookings twice due to the carry over. 

 

Not sure if I have explained this a bit better. 

 

Thanks for your help. I really appreciate it. 

 

Super User I
Super User I

Re: Cumulative Reset after a value is reached

@corange  any chance you can provide a sample data in a format that can be copy pasted in excel and clearly show your desired outcome in a separate column?

corange Helper IV
Helper IV

Re: Cumulative Reset after a value is reached

Hi,

 

I believe I need a new measure that will add my carry over from the month prior to the current month. 

 

E.g. AUGUST = 368 + 53

 

I will then have to modified my quotient formula with the new measure to get the right result. 

Highlighted
corange Helper IV
Helper IV

Re: Cumulative Reset after a value is reached

Hi, 

 

Please find attached the document with a couple of examples and the desired outcome. 

 

https://capitaltransport-my.sharepoint.com/:x:/g/personal/corange_capitaltransport_com_au/EbyukQVxx5...

Super User I
Super User I

Re: Cumulative Reset after a value is reached

@corange  can't get in the sharepoint as I am not in this sharepoint directory. Can you share this by G drive

Super User IV
Super User IV

Re: Cumulative Reset after a value is reached

The should be one cumulative total which does not reset

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

 

Not use this for both points and price.

Points = MOD([Cumm Sales],150)

Rewards = Quotient([Cumm Sales],150)

 

In case you want to move around date try, this one I have not tested. Say take point one month ahead or behind.

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,dateadd(date[date],1,month) <=maxx(date,date[date])))

 

To Share: You can create dummy data in excel and try to replicate your output on that and share the excel.

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors