Forum Discussion
Cumulative Total Disappearing
Why is it that my cumulative total dispaers from any chart when it is not acompanied by some dort of date? For example if i have a table and in one column i have date and the other the cumulative totalit will display fine, but if i take out date culomn cumulative total will disapear.
Ok!
| Year | Cumulative Total |
| 2019 | 200 |
Not Ok!
| Cumulative Total | |
| Blank |
How can i display cumulative without any date biside it?
Thank you
Hi, Anonymous
According to your description and sample data, I guess your problem is that you didn’t put the [Year] column into the slicer so that the measure can’t start counting from the month you selected of the selected year, I created some data and create a measure to get the result you want to get, you can take a look of my method:
This is my test data(across two years):
- I created a Calendar table to put into the Slicer:
00_Dates_Fiscal = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))- Then I created this measure:
Test_YTD_2 = var _selectedyear=YEAR(MIN('00_Dates_Fiscal'[Date])) var _selectedmonth=MONTH(MIN('00_Dates_Fiscal'[Date])) var _result= CALCULATE( SUM('33_Master_DB'[Total]), FILTER( ALLSELECTED('33_Master_DB'), YEAR([CalDate])=_selectedyear&& MONTH([CalDate])>=_selectedmonth&& MONTH([CalDate])<=MAXX('33_Master_DB',MONTH([CalDate])))) return IF(MAXX('33_Master_DB',YEAR([CalDate]))=_selectedyear,_result,BLANK())- Then I created a Slicer and a table chart, and place columns like this:
And I guess this is what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- themistoklis
Community Champion
Anonymous
My understanding is that cumulative values are based on a dimension. So it doesnt make sense to have cumulative values but no dimension assigned to it.
The second table simply shows the total.
Also one possible reason that you get blank value is that on date field (or the field that you use inside the cumulative formula) there may be blank values.
Try filter them out e.g. from Filters pane and check if value appears
Hi themistoklis
Thanks for the quick reply, there will be other dimensions assigned to it like country/ Businsess unit / ETc... the issue that no data will display if there is no date field(s) included in the table. I just want to show YTD as of selected date in a slicer.
- amitchandak
Super User
Anonymous , Not clear. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
You can also have formula on year column
cumm = calculate(sum(Table[value]), filter(allselected(Table), Table[Year] <=max(table[Year])))
amitchandak themistoklis
This is the code im using:Test_YTD_2 = VAR PRYEAR = [PY] RETURN CALCULATE( SUM('33_Master_DB'[Total]), '33_Master_DB'[FiscalYear] = PRYEAR, '33_Master_DB'[Source] = "System1", '33_Master_DB'[Category] = "Actuals", ALL('00_Dates_EST'[MonthName]), '11_End_Regions'[Region] <>BLANK(), FILTER(ALL('00_Dates_Fiscal'[Date]), MONTH ( '00_Dates_Fiscal'[Date] ) <= MONTH ( MAX ( '00_Dates_Fiscal'[Date] ) )) )This is what its retuning (Dummy Numbers):
CalMonth **bleep** Total Apr 3020.4191 May 3982.8677 Jun 4858.2111 Jul 5363.6897 Aug 5902.8366 Sep 6796.724 Oct 8120.1152 Nov 8778.5402 Dec 9983.18 Jan 763.5746 Feb 1317.6819 Mar 2330.369 As you can see intead of starting to sum from april, APR already contains the sum of JAN,FEB,MAR,APR i need it to start counting from APR as its first month. Please let me know what can i do to above code to make it work.
Using DATESYTD & TOTALYTD does not work for me.
Thank you
- v-robertq-msft
Community Support
Hi, Anonymous
According to your description and sample data, I guess your problem is that you didn’t put the [Year] column into the slicer so that the measure can’t start counting from the month you selected of the selected year, I created some data and create a measure to get the result you want to get, you can take a look of my method:
This is my test data(across two years):
- I created a Calendar table to put into the Slicer:
00_Dates_Fiscal = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))- Then I created this measure:
Test_YTD_2 = var _selectedyear=YEAR(MIN('00_Dates_Fiscal'[Date])) var _selectedmonth=MONTH(MIN('00_Dates_Fiscal'[Date])) var _result= CALCULATE( SUM('33_Master_DB'[Total]), FILTER( ALLSELECTED('33_Master_DB'), YEAR([CalDate])=_selectedyear&& MONTH([CalDate])>=_selectedmonth&& MONTH([CalDate])<=MAXX('33_Master_DB',MONTH([CalDate])))) return IF(MAXX('33_Master_DB',YEAR([CalDate]))=_selectedyear,_result,BLANK())- Then I created a Slicer and a table chart, and place columns like this:
And I guess this is what you want.
You can download my test pbix file here
If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
