cancel
Showing results for
Did you mean:
Highlighted
Helper IV

## Cumulative per day

I have this report that show my income and expenses per day in my two accounts. Bank and Cash (my pocket).

 Per day 01/may 02/may 03/may 04/may 05/may Bank Cash Bank Cash Bank Cash Bank Cash Bank Cash Income 50,00 63,00 60,00 43,00 430,00 444,00 4,00 56,00 13,00 43,00 Expense -      40,00 -      50,00 -        4,00 -      56,00 -    500,00 -        2,00 -       12,00 -      98,00 Total 50,00 23,00 10,00 39,00 374,00 -      56,00 2,00 56,00 1,00 -      55,00

I want to be able to work with cumulative, showing information like this:

 Cumulative 01/mai 02/mai 03/mai 04/mai 05/mai Bank Cash Bank Cash Bank Cash Bank Cash Bank Cash Total 50,00 23,00 60,00 62,00 434,00 6,00 436,00 62,00 437,00 7,00

So, that means I have the result of the amount of money I have on each account. So could also make a repost of cumulative of the total, after. But I also need detailed so I can follow of the accounts.

 Total in account Bank Cash Total 437,00 7,00

My problem is actually the second report. First one is ready to go... 🙂

Thanks as usual!

Efilipe

1 ACCEPTED SOLUTION

Accepted Solutions
Resolver III

## Re: Cumulative per day

You can accomplish cumulative totals using measures.  Say I have a standard measure called Revenue:

`Revenue = SUM(Sales[Order Revenue])`

I can calculate a cumulative total measure using the following:

`Cumulative Revenue Full = CALCULATE([Revenue],FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])))`

What this does is filter your date table to include all dates before or equal to whatever date you're displaying in your visual/table/matrix and then runs the calculation.  This will always give the cumulative total going back as far as your entire dataset goes back in time.

I like my own variation where I base the start of the cumulation on the earliest date in the current context.  So if I have sales data going back to 2010, but I've used a slicer or other filter to show only sales for 2016+ in my table, it will consider 1/1/2016 to be the first day of the accumulated total.  This is the DAX formula:

```Cumulative Revenue =
VAR EarliestDate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED())
RETURN CALCULATE([Revenue],FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] >= EarliestDate))```

And here's a sample table where I include revenue, Cumulative Revenue Full (goes back to my earliest sales data) and Cumulative Revenue that only goes back to 1/1/2016 because of my slicer.  Notice that Revenue and Cumulative Revenue are the same value for 1/1/2016 but then start to accumulate going forward:

If I go back one more day to 12/31/2015, you'll see that the Cumulative value starts from that date's value and adds from there instead of 1/1/2016:

3 REPLIES 3
Helper IV

## Re: Cumulative per day

I think copying and pasting doesnt take my merge cell formating. But you guys can notice that each day has 2 columns. Bank and Cash 🙂

Resolver III

## Re: Cumulative per day

You can accomplish cumulative totals using measures.  Say I have a standard measure called Revenue:

`Revenue = SUM(Sales[Order Revenue])`

I can calculate a cumulative total measure using the following:

`Cumulative Revenue Full = CALCULATE([Revenue],FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date])))`

What this does is filter your date table to include all dates before or equal to whatever date you're displaying in your visual/table/matrix and then runs the calculation.  This will always give the cumulative total going back as far as your entire dataset goes back in time.

I like my own variation where I base the start of the cumulation on the earliest date in the current context.  So if I have sales data going back to 2010, but I've used a slicer or other filter to show only sales for 2016+ in my table, it will consider 1/1/2016 to be the first day of the accumulated total.  This is the DAX formula:

```Cumulative Revenue =
VAR EarliestDate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED())
RETURN CALCULATE([Revenue],FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] >= EarliestDate))```

And here's a sample table where I include revenue, Cumulative Revenue Full (goes back to my earliest sales data) and Cumulative Revenue that only goes back to 1/1/2016 because of my slicer.  Notice that Revenue and Cumulative Revenue are the same value for 1/1/2016 but then start to accumulate going forward:

If I go back one more day to 12/31/2015, you'll see that the Cumulative value starts from that date's value and adds from there instead of 1/1/2016:

Resolver III

## Re: Cumulative per day

I would note that there are a ton of "period to date" functions that are going to be much simpler and faster to use to accomplish things like Month To Date, Quarter To Date, Year To Date.  Check out the following for more information on using all the different time patterns with DAX.  But you can accomplish the cumulative totals as I described above as well if you really need it to be that flexible.

DAX Time Patterns

DAX Cumulative Total Pattern

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors