Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
2 years ago
Solved

Linear trend chart using baseline data

Hi,

 

I have tasks defined by projects at start of the year and month on month actuals will be added. Need to plot a chart to actuals in one line and another one linear trend till start of the next year (i.e Jan'25). Attached data and out snapshot for reference

The liner line needs to be configured. it could be of 6% or 8%. The measure should work on Project filter as well when applied.

MonthYearProjectTasks
24-Jan 250
24-Feb 251
24-Mar 220
24-Apr 220
24-May  
24-Jun  
24-Jul  
24-Aug  
24-Sep  
24-Oct  
24-Nov  
24-Dec  
25-Jan  

 

General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

     

    Thanks for the reply from lbendlin , please allow me to provide another insight: 

    Hi  manojk_pbi ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =
var _date=
SUMMARIZE('Table','Table'[Month],"mindate",MINX(FILTER(ALL('Table'),'Table'[Month]=EARLIER('Table'[Month])),[Date]))
return
ADDCOLUMNS(
    _date,"rank",RANKX(_date,[mindate],,ASC))

    2. Select [Month] – Column tools – Sort by column – [rank].

    3. Create measure.

    value =
var _index=MINX(ALL('Table 2'),'Table 2'[rank])
var _month=MINX(FILTER(ALL('Table 2'),'Table 2'[rank]=_index),[Month])
var _value=
SUMX(FILTER(ALL('Table'),'Table'[Month]=_month),[Tasks])
RETURN
IF(
    MAX('Table 2'[rank])= _index,_value,
    _value-
_value * (MAX('Table 2'[rank])-1) * 0.008)

    4. Result:

     

     

    Best Regards,

    Liu Yang

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

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    This burndown chart pattern has been discussed quite frequently here in the past couple weeks.  Have you tried the search function?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

     

    Thanks for the reply from lbendlin , please allow me to provide another insight: 

    Hi  manojk_pbi ,

    I created some data:

    Here are the steps you can follow：

    1. Create calculated table.

    Table 2 =
var _date=
SUMMARIZE('Table','Table'[Month],"mindate",MINX(FILTER(ALL('Table'),'Table'[Month]=EARLIER('Table'[Month])),[Date]))
return
ADDCOLUMNS(
    _date,"rank",RANKX(_date,[mindate],,ASC))

    2. Select [Month] – Column tools – Sort by column – [rank].

    3. Create measure.

    value =
var _index=MINX(ALL('Table 2'),'Table 2'[rank])
var _month=MINX(FILTER(ALL('Table 2'),'Table 2'[rank]=_index),[Month])
var _value=
SUMX(FILTER(ALL('Table'),'Table'[Month]=_month),[Tasks])
RETURN
IF(
    MAX('Table 2'[rank])= _index,_value,
    _value-
_value * (MAX('Table 2'[rank])-1) * 0.008)

    4. Result:

     

     

    Best Regards,

    Liu Yang

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