Line Chart DAX

[Anonymous]

Hello,

I'm once again stumped, trying to achieve what I think should be a basic result. I am trying to get a line chart to extend beyond the max date for which I have data, displaying the a flat line that reflects the final data point all the way to the max date set in the x-axis. See screen shot below. My grey line chart ends on the last "Update Date" to the repeating table query with a SUM of 124 story points. I'm trying to get that line to extend with the 124 value to the end of the date range (in this case 1/11/23). Any DAX suggestions to achieve this would be much appreciated!

Here is the basic measure I'm using fro the current stepped grey line:

Estimated Story Points2(hist) =

SUM(stg_jira_product_team_stories_hist[Story Points])

Here is the query the measure references. This table is related to a Date table using the "Update Date" field.

Thank you!

[v-xiaosun-msft]

Hi @Anonymous ,

According to your description, I made the sample and please follow theses steps.

Sample data:  ("Table")

Firstly, create a new table to return the max date of "Updated Date".

Table 2 = ADDCOLUMNS('DATE',"1",MAX('Table'[updated date]))

Then create a calculated column to return the dates of x-axis which is later than the max date of "Updated Date".

Column = IF('Table 2'[Date]>='Table 2'[1],'Table 2'[Date])

We will get the "Table2" as below.

Then in "Table", create two measures.

Measure 3 = 
CALCULATE(SUM('Table'[points]),FILTER(ALL('DATE'[Date]),'DATE'[Date]>=MAX('Table 2'[1])))

Measure 4 = 
CALCULATE('Table'[Measure 3],FILTER('Table 2',MAX('DATE'[Date])='Table 2'[Column]))

Put measure 4 onto the line chart, you will get the expected output.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-xiaosun-msft, Thank you so much for providing a solution!  I believe I have replicated your solution using my sources.  

Step 1:

Table 2 = ADDCOLUMNS('Dates',"1",MAX(stg_jira_product_team_stories_hist[Update Date]))

Step 2:

Measure 3 =

CALCULATE(SUM(stg_jira_product_team_stories_hist[Story Points]),FILTER(ALL('Dates'[Date]),'Dates'[Date]>=MAX('Table 2'[1])))

Step 3:

Measure 4 =

CALCULATE([Measure 3],FILTER('Table 2',MAX('Dates'[Date])='Table 2'[2]))

As you can see below, I'm getting a different result. For reasons I don't understand, Measure 4 is producing the SUM (total) of all Story Points from all days prior to today, and then the current total for dates => today. Can you see anything in the DAX that is causing this?