Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Line Chart DAX

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!

Need Help

2 Replies

  • v-xiaosun-msft's avatar
    v-xiaosun-msft
    Community Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?