Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
corange
Post Patron
Post Patron

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

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.

View solution in original post

15 REPLIES 15
smpa01
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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. 

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

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Hi,

 

No worries. Here you are : https://docs.google.com/spreadsheets/d/1j96YXXCbobiUCz_sXiXedIZKxlSE_tcODF9_xYxJzKI/edit?usp=sharing

 

I really hope you can help me here. I know it can be done but I have got no clue where to have the carry over points changed based on that 150 bracket. 

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.

Hi, Thank you. 

 

It did work. For me to obtain the specific number of prizes to be given in a month, I calculated the number of tickets that were received the month prior and substracted it. 

Yes it is 56.

 

I am goign to try your solution now. The only thing I dont want it to do is to do a cummulative of the prizes. At the moment your solution keeps adding the prize from the previous month to the current and so on. I would like to just have what they need to be given that month. Can I use an aggregation and substract what was given last month to the current? 

@corange see if it works for you

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Hi, 

 

Thank you. I have tried your solution and looked at the results you obtained for the prizes per month but it is not matching what I am expecting. Like in the first date for example, we should only have 2 prize then the carry over but your example shows 3. 

amitchandak
Super User
Super User

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

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. 

 

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

 

The quotient should tell no of price

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. 

 

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.