Forum Discussion
Cumulative Total till today
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..
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
Alpha1029It needs to be created as a measure, not a calculated column. The measure can be added to a visual table as a column.
11 Replies
- dedelman_clng
Community Champion
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
Hi David
I tried the way you suggested and I got zeros in the Running Total Column.
- dedelman_clng
Alpha1029It needs to be created as a measure, not a calculated column. The measure can be added to a visual table as a column.
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)
- 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-TITHW/m-p/434008 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
