cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
efilipe Helper IV
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 day01/may02/may03/may04/may05/may
 BankCashBankCashBankCashBankCashBankCash
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:

 

Cumulative01/mai02/mai03/mai04/mai05/mai
 BankCashBankCashBankCashBankCashBankCash
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 accountBankCash
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
bblais Resolver III
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:

Capture.PNG

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:

 

Capture2.PNG

View solution in original post

3 REPLIES 3
efilipe Helper IV
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 🙂

bblais Resolver III
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:

Capture.PNG

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:

 

Capture2.PNG

View solution in original post

bblais Resolver III
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

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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