Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative Total Hiding Future Months

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

  • Anonymous's avatar
    Anonymous
    7 years ago
    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)

9 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    7 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        parry2k
        Icon for Super User rankSuper User
        7 years ago

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