Cumulative Total till today

[Alpha1029]

Hi 

I want to get cumulative totals according to the dates till today and for all the future dates cumulative total should be shown as zero or blank. I went through some examples but those did not work for me. 

I have seen the code below

CALCULATE(Measure,Filter(All(Table),Table[Date]<=max(Date)). 

I replaced max(Date) with Today() but it did not work. 

I want to see cumulative total as in picture . Appreciate your help...  Thanks in advance..

[dedelman_clng · Accepted solution]

Try this:

Running Total =
var __MaxDT = max(Table[Date])
var __Rtotal = 
  CALCULATE(SUM(Table[Value]),
    Filter(All(Table), Table[Date] <= __MaxDT)
)

RETURN
IF (__MaxDT > TODAY(), 0, __Rtotal)

Hope this helps

David

[chad_mckaskle]

Sorry to be replying back to an old post, but the answer provided for this has worked great for me. However, i'm running into an issue where it is not providing a cumulative value if there was not a value for that given date. For example, if there was no value entered for 12/12/2018, the running total field is coming back blank, but then correctly picking back up on 12/13/2018. Is there a way to adjust the formula to return the running total even if there is a value missing on a given date?
Modifying the table originally provided to the below and using the measure formula i am getting the following:

Date	Value	RunningTotal
12/11/2018	100	100
12/12/2018
12/13/2018	100	200
12/14/2018	100	0
12/15/2018	100	0
Totals	400

However, i'd like for the running total to show as '100' for the 12/12/2018 date even though there is not any additional value to add.

Note: The values that i am actually using are being applied to the first day of the month. An example of my data is in the image below. The formula correctly totals the cumulative, but since there is no data entered for month 10/1/2020 the formula provides a 'blank' value in the 'Cumulative plan' field, but i'd still like the formula to return a value '534,721' like in the image below:

Thanks

Chad

[dedelman_clng · Accepted solution]

Try this:

Running Total =
var __MaxDT = max(Table[Date])
var __Rtotal = 
  CALCULATE(SUM(Table[Value]),
    Filter(All(Table), Table[Date] <= __MaxDT)
)

RETURN
IF (__MaxDT > TODAY(), 0, __Rtotal)

Hope this helps

David

[naveenanavi649]

Thank you! This really helped me today 

[Alpha1029]

@Greg_DecklerI have another scenario,where the underlined part in the code below is a measure, not a column. Below code works fine if we use column. What should be done for measure

Thanks in advance...

Running Total = var __MaxDT = max(Table[Date])

var __Rtotal = CALCULATE(SUM(Table[Value]), Filter(All(Table), Table[Date] <= __MaxDT) )

RETURN

IF (__MaxDT > TODAY(), 0, __Rtotal)

[Alpha1029]

Hi David 

I tried the way you suggested and I got zeros in the Running Total Column. 

[dedelman_clng · Accepted solution]

@Alpha1029It needs to be created as a measure, not a calculated column.  The measure can be added to a visual table as a column.

[Pow21er]

Hi. I have a question. I tried this and it worked but ı have a question. I want to see total of 3th,4th and 5th row in 3th row. Other fields okey but i didnt solve this problem.

Details: I have a checklist and i want to see cumulative toplam till today of status column. I made it but sometimes we are log in data for future and i cant see on the table. So ı want to total for today.

[Alpha1029]

Thanks a lot for the help. It works fine now.

[Alpha1029]

@Greg_Deckler  I tried to create it as measure and still it gives me all zeros . Any suggestions...

[Alpha1029]

@Greg_DecklerIt works if I create it as a measure. I was checking it in the wrong place.  Sorry for coming to a conclusion so quickly. 

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...