Cumulative Total Disappearing

[Anonymous]

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

[v-robertq-msft · Accepted solution]

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):

1. I created a Calendar table to put into the Slicer:

00_Dates_Fiscal = CALENDAR(DATE(2020,1,1),DATE(2021,12,31))

2. 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())

3. 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.

[Anonymous]

@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

[amitchandak]

@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])))

[themistoklis]

@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

[Anonymous]

@themistoklis 

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.