cancel
Showing results for
Did you mean:
New Member

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

2 ACCEPTED SOLUTIONS
Super User II

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

Proud to be a Super User!

Super User II

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

Proud to be a Super User!

8 REPLIES 8
Super User II

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

Proud to be a Super User!

New Member

@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

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)

New Member

Hi David

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

Super User II

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

Proud to be a Super User!

New Member

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

New Member

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

New Member

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

Super User IV

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

---------------------------------------

I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements