Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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)

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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.

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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
Not applicable

@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
Not applicable

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)

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
Not applicable

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

Anonymous
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors