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

Running totals with if else and max value

Hi,

 

I want to create a table about leave and need a cummulative total.

Actually I already using a measure in here.

And here is the result.

 

2017-09-12_1148.png

 

I want in the Annual Leave (Column or Measure) the data max 20. So it will be like this

2017-09-12_1158.png

 

p.s. the Annual Leave column is the column that I created manually and I expect the result as the Annual Leave column

 

I already try to create new column with this power query:

Annual Leave Column2 = 
IF((CALCULATE (
    SUM ( Sheet1[Debit/Credit] ),
    ALL ( Sheet1 ),
   Sheet1[Date] <= EARLIER ( Sheet1[Date] )
))>20, 20,CALCULATE (
    SUM ( Sheet1[Debit/Credit] ),
    ALL ( Sheet1 ),
   Sheet1[Date] <= EARLIER ( Sheet1[Date] )
))

But the result when it already 20 and there is a -1 in next date it will still count 20 and stuck in 20. 

 

 

Any help would be appreciate 🙂

 

Thanks,

Regards,

Connie

 

21 REPLIES 21

yes that is what is needed - will open a new post on this topic....

@conniedevina

www.CahabaData.com

Answered by @MarcelBeug in this link.

 

Thanks a lot for the answer and here is the answer

 

using power query in advanced editor with this query

let
    Source = Excel.Workbook(File.Contents("D:\Projects\Internal CRM\Leave Data.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Type", type text}, {"Debit/Credit", type number}}),
    RunningSum = List.Skip(List.Accumulate(#"Changed Type"[#"Debit/Credit"],{0},(sum,value) => sum & {List.Min({12,List.Last(sum)+value})})),
    TableWithRunningSum = Table.FromColumns(Table.ToColumns(#"Changed Type")&{RunningSum},Value.Type(Table.AddColumn(#"Changed Type","Running Sum", each 0, type number)))
in
    TableWithRunningSum

 And thanks for @CahabaData for helping me until I get this answer.

 

Thank you!

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors