cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Cumulative total by date

Hello,

I am trying to get two running totals as example below.

First one is for the toral up to yesterday (Today-1)

Second one is for the total on&after today.

I tried these DAX...but doesnt work.

Running total 1 :=
CALCULATE (
[Total Sales],
FILTER (
ALL ( DimDate[Datekey] ),
DimDate[Datekey] < Today( ( DimDate[Datekey] ) )
)
)

Running total 2 :=
CALCULATE (
[Total Sales],
FILTER (
ALL ( DimDate[Datekey] ),
DimDate[Datekey] >= Today( ( DimDate[Datekey] ) )
)
)

Any help would be greatly appreciated!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: Cumulative total by date

@topazz11 there are few ways to do this, here is one,  add following measures

```Upto Today = CALCULATE( [Sum Item1], FILTER(
ALL( 'Date'[Date1] ),
'Date'[Date1] <=  MAX( 'Date'[Date1] )) )
* IF( MAX( 'Date'[Date1] ) < TODAY(), 1 )

From Today = CALCULATE( SUM( 'Table'[Item2] ),
FILTER( ALL( 'Date'[Date1] ), 'Date'[Date1] <= MAX( 'Date'[Date1] ) &&
'Date'[Date1] >= TODAY() ))*
DIVIDE( [Sum Item2], [Sum Item2])```

sum item1 and sum item 2 measures are just sum of respectice columns

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Super User

## Re: Cumulative total by date

Have you tried the built-in running total quick measures? Also, 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 book! Learn Power BI from Packt

Proud to be a Datanaut!

2 REPLIES 2
Super User

## Re: Cumulative total by date

@topazz11 there are few ways to do this, here is one,  add following measures

```Upto Today = CALCULATE( [Sum Item1], FILTER(
ALL( 'Date'[Date1] ),
'Date'[Date1] <=  MAX( 'Date'[Date1] )) )
* IF( MAX( 'Date'[Date1] ) < TODAY(), 1 )

From Today = CALCULATE( SUM( 'Table'[Item2] ),
FILTER( ALL( 'Date'[Date1] ), 'Date'[Date1] <= MAX( 'Date'[Date1] ) &&
'Date'[Date1] >= TODAY() ))*
DIVIDE( [Sum Item2], [Sum Item2])```

sum item1 and sum item 2 measures are just sum of respectice columns

Proud to be a Datanaut! Appreciate your Kudos
Feel free to email me with any of your BI needs.

Super User

## Re: Cumulative total by date

Have you tried the built-in running total quick measures? Also, 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 book! Learn Power BI from Packt

Proud to be a Datanaut!

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 4,989 guests
Recent signins: