cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alpha1029
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..

Captur.PNG

2 ACCEPTED SOLUTIONS
dedelman_clng
Super User II
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)

2018-12-13 12_59_46-new scratchpad - Power BI Desktop.png

Hope this helps

David




Proud to be a Super User!




View solution in original post

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




View solution in original post

8 REPLIES 8
dedelman_clng
Super User II
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)

2018-12-13 12_59_46-new scratchpad - Power BI Desktop.png

Hope this helps

David




Proud to be a Super User!




View solution in original post

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

 

 

Hi David 

 

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

 

 

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




View solution in original post

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

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

@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
Super User IV
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...


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors