topic Re: Cumulative Total Hiding Future Months in DAX Commands and Tips

Cumulative Total Hiding Future Months

Anonymous — Tue, 25 Jun 2019 09:42:55 GMT

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

Re: Cumulative Total Hiding Future Months

parry2k — Tue, 25 Jun 2019 13:31:54 GMT

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

Re: Cumulative Total Hiding Future Months

Anonymous — Tue, 25 Jun 2019 13:45:56 GMT

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.

Re: Cumulative Total Hiding Future Months

parry2k — Tue, 25 Jun 2019 16:21:24 GMT

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

Re: Cumulative Total Hiding Future Months

Anonymous — Mon, 01 Jul 2019 12:46:03 GMT

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

Re: Cumulative Total Hiding Future Months

Anonymous — Mon, 01 Jul 2019 14:47:35 GMT

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)

Re: Cumulative Total Hiding Future Months

Anonymous — Mon, 01 Jul 2019 16:03:23 GMT

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.

Re: Cumulative Total Hiding Future Months

Anonymous — Mon, 01 Jul 2019 16:20:45 GMT

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)

Re: Cumulative Total Hiding Future Months

Anonymous — Mon, 01 Jul 2019 16:24:45 GMT

Anonymous: Perfect - thanks so much for your help.

Re: Cumulative Total Hiding Future Months

evanj — Mon, 25 May 2020 01:48:43 GMT

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)