Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
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:
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:
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:
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |