Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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

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

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 🙂

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.