Forum Discussion

manojk_pbi's avatar
manojk_pbi
Icon for Helper V rankHelper 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...
  • 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