Cumulative Total Hiding Future Months

[Anonymous]

Hi,

I need to create a pivot table and line graph in Excel that shows a cumulative total by month for a five year period. The issue is that I need the pivot table to hide future values/show future values as blank so that my line graph does not extend as a stright horizontal line to the end of the year but stops at a certain month.

I have a date table linked up to my single data table and would prefer to do this using only measures but would also consider using a combination of measures and calculated columns if that simplifies the process.

How can I go about doing this?

Thanks

[parry2k]

@Anonymous can you share your DAX expression you have created and I can get you the trick to fix it.

[Anonymous]

Thanks parry2k,

My formula is as follow (excuse the formatting if incorrect):

Cumulative E&A Wells:=CALCULATE(COUNTROWS('FM Full Export'),

            FILTER(ALLSELECTED('Calendar'[Month]),

                     ISONORAFTER('Calendar'[Month],

                     MAX('Calendar'[Month]),DESC)))

This gives me the correct cumualtive total for 2014 - 2019 but I need a null value for July - December 2019 in my pivot table.

[parry2k]

@Anonymous try this, add a measure for row count, I called it My Row Count

My Row Count = COUNTROWS('FM Full Export')

Cumulative E&A Wells:=CALCULATE([My Row Count],
            FILTER(ALLSELECTED('Calendar'[Month]),
                     ISONORAFTER('Calendar'[Month],
                     MAX('Calendar'[Month]),DESC))) *
DIVIDE( [My Row Count], [My Row Count] )

[Anonymous]

Cumulative E&A Wells :=
var __currentMaxDate = max('Calendar'[Date])
var __currentMinDate = min('Calendar'[Date])
var __today = TODAY()
var __cumulativeValue =
	CALCULATE (
	    COUNTROWS ( 'FM Full Export' ),
	    'Calendar'[Date] <= __currentMaxDate,
	    ALLSELECTED ( 'Calendar'[Date] )
	)
var __shouldDisplay =
	 -- check if the current period is
	 -- fully contained in the future
	NOT ( _currentMinDate >= __today ) -- check which you want >= or >
return
	if( __shouldDisplay, __cumulativeValue)

[Anonymous]

@Anonymous - so close. The formula is giving me a cumulative total over the entire 2014 - 2019 period, whereas I need a new cumulative total for each year. 

Everything else works as requred. 

[Anonymous · Accepted solution]

Cumulative E&A Wells :=
var __currentMinDate = min('Calendar'[Date])
var __today = TODAY()
var __cumulativeValue =
	CALCULATE (
	    COUNTROWS ( 'FM Full Export' ),
	    DATESYTD( 'Calendar'[Date] ),
	    ALLSELECTED ( 'Calendar'[Date] )
	)
var __shouldDisplay =
	 -- check if the current period is
	 -- fully contained in the future
	NOT ( _currentMinDate >= __today ) -- check which you want >= or >
return
	if( __shouldDisplay, __cumulativeValue)

[evanj]

Hi, trying to do something similar but QTD.  But I am getting blanks/gaps in the data where there are no sales duing the quarter, like the weekends.  How do I get continuous data filling these gaps?

Cumulative Rev QTD:=var __currentMinDate = min('Calendar'[Date])
var __today = TODAY()
var __cumulativeValue =
CALCULATE (
sumx([Unit Price]*[Shipped Quantity]),
DATESQTD( 'Calendar'[Date] ),
ALLSELECTED ( 'Calendar'[Date] )
)
var __shouldDisplay =
-- check if the current period is
-- fully contained in the future
NOT ( __currentMinDate >= __today ) -- check which you want >= or >
return
if( __shouldDisplay, __cumulativeValue)

[Anonymous]

@Anonymous: Perfect - thanks so much for your help.

[Anonymous]

@parry2k 

Many thanks for your help so far and apologies for the kate reply. I think the formula is almost correct but not quite.

Using your suggestion I have managed to get a cumulative total by month for each year but only when the months are ordered alphabetically. When ordered chronologically the cumulative totals are all out of place. 

I also need a 0 (zero) in months with no change so that my graph follows a horizontal line rather than dropping to the x axis.

Thanks