cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jasonwhurley Regular Visitor
Regular Visitor

How to make Line Chart stop mid-graph for future dates

I've made a line chart showing 3 lines, each is a Percentage (y-axis) over Time (x-axis):

1. A "goal" line that has pre-populated data (see black line below)

2. Two data lines showing progress related to that goal (red and green lines below)

 

The data lines do not have values for future dates and I'd like that line just to stop mid-graph after today's date.  But, I can't figure out how because I want the goal line to continue into the future.

 

I'm importing data from Excel and have tried 0's for future values, but that looks bad as the line just jumps down to the x-axis where y=0 (see screenshot below).  And I've tried blanks ("") and NA() values in Excel, but they cause an data mismatch error in Power BI because it's set to a Percentage.

 

PBIDesktop_2017-03-07_08-53-58.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Reid_Havens Member
Member

Re: How to make Line Chart stop mid-graph for future dates

Hi Jason,

 

Happy to help. If this is a numerical column then you might want to try using the BLANK() function. My first thought would be to write an IF statement asking of the Date on your axis is less than or equal to today's date. If true then it would return the value from your formula, if false it would return BLANK(). So something potentially like this. Let me know if this helps or gets you closer.

 

=IF( Table[Date] <= TODAY(), [Formula], BLANK() )

View solution in original post

5 REPLIES 5
Reid_Havens Member
Member

Re: How to make Line Chart stop mid-graph for future dates

Hi Jason,

 

Happy to help. If this is a numerical column then you might want to try using the BLANK() function. My first thought would be to write an IF statement asking of the Date on your axis is less than or equal to today's date. If true then it would return the value from your formula, if false it would return BLANK(). So something potentially like this. Let me know if this helps or gets you closer.

 

=IF( Table[Date] <= TODAY(), [Formula], BLANK() )

View solution in original post

jasonwhurley Regular Visitor
Regular Visitor

Re: How to make Line Chart stop mid-graph for future dates

This is great, thanks so much @Reid_Havens!  Bottom line is, I was trying to do too much data prep in Excel, when I should have just done it in Power BI.

 

The one tweak I had to make to your formula was to make the Logical Test of the IF statement an OR to ensure my "Goal Line" is included for the future dates.   So, it's something like:

 

=IF( OR( Table[Date] <= TODAY(), Table[Name]="Goal"), [Formula], BLANK() )

Reid_Havens Member
Member

Re: How to make Line Chart stop mid-graph for future dates

Fantasic. Good evolution of the formula and glad you were able to find a solution.

acrow Occasional Visitor
Occasional Visitor

Re: How to make Line Chart stop mid-graph for future dates

Hi, I have managed to get my cumulative measure to stop at a particular point (Cost Period = 4) and fill the future with BLANK()

 

However when plotting using Line Chart the Yellow line just goes horizontal (flat line) even though values are BLANK.

 

Can anyone advise what I am doing wrong?

 

Cumulative - STOP mid-graph.PNGMax Cumulative Earned Cuttoff.PNG

 

Highlighted
acimisliin Frequent Visitor
Frequent Visitor

Re: How to make Line Chart stop mid-graph for future dates

Hi,

Please check this DAX measure:

 

Cumulative NSI = IF( MAX('Calendar'[Date]) > TODAY(), BLANK(),
CALCULATE(
SUM(AllBusinessSales[NSI]),
FILTER(
ALLSELECTED( 'Calendar' ),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)
)
 
The trick is to put BLANK() after today.
 
 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 201 members 2,352 guests
Please welcome our newest community members: