Add trend line along with MoM data

[manojk_pbi]

Hi Friends,

I am looking for suggestion on one of my requirement, please help me. 

Requirement is to draw a chart with Month on Month trends of Open Bugs and a trend to show burndown of the bugs with 10%. Below is the sample data with expected out in line chart. Need you advise to write measure to calculate Bugs@10%  a trend line. How this can be made dynamic w.r.t start of the month & % values used to calculate.

MY	Bugs#	Bugs @10%
Jan-23	1200
Feb-23	1100	1080
Mar-23	1050	972
Apr-23	1025	875
May-23	950	787
Jun-23	940	709
Jul-23	1000	638
Aug-23	900	574
Sep-23	850	517
Oct-23	880	465
Nov-23	800	418
Dec-23	700	377

You suggestions are much appreciated

Thanks,

[manojk_pbi]

Thanks for the solution. Solution is very dynamic and perfectly meets my requirement. I will try to understand the measure written and functions used.

Can i ask here how can we make this into look like forecast to predict how the burndown looks when the we go with different percentages. eg I have data for Jan'24 & Feb'24, how can we draw a chart to show where it will reach by dec'24 if we go with N%.

[lbendlin]

You can duplicate the measure and fix it at certain percentages, say 5% or 10%, and add that as a line in the chart - ideally a dashed line.

[manojk_pbi]

Hi @lbendlin , I did tried duplicating the measure but it didn't work. May be i was not clear in my question in previous post. 

What i am trying is to show the data for Previuos & current years scenarios,

Previous year : what could be my burndown if specific percentage selected against the actual - This is working as expected

Current Year : What will be my forecasted burndown for next 12 months - I am not able to get 

This should work on same data sets, when selected 2024 it should populate brundown for 12 months considering the Jan'24 as baseline

This is what i am getting, but not able to add it for 12 months

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[manojk_pbi]

HI @lbendlin ,

Here i am attaching complete set of sample data and the required out put. Please suggest how the existing measure can be modiy to get the required output.

I would need filter on Year and Product. On all filter the data should work and provide burndown trend.

Sample Data

MY	Product	Bugs#	Bugs @10%
Jan-23	Product1	1200
Feb-23	Product1	1100	1080
Mar-23	Product1	1050	972
Apr-23	Product1	1025	875
May-23	Product1	950	787
Jun-23	Product1	940	709
Jul-23	Product1	1000	638
Aug-23	Product1	900	574
Sep-23	Product1	850	517
Oct-23	Product1	880	465
Nov-23	Product1	800	418
Dec-23	Product1	700	377
Jan-24	Product1	650
Feb-24	Product1	600	585
Mar-24	Product1	580	527
Apr-24	Product1	590	521
May-24	Product1		516
Jun-24	Product1		511
Jul-24	Product1		506
Aug-24	Product1		501
Sep-24	Product1		496
Oct-24	Product1		491
Nov-24	Product1		486
Dec-24	Product1		481

[lbendlin]

see attached

[manojk_pbi]

Hi @lbendlin , thanks for you quick response.

I think my question was not clear as i didn't mention few points clearly, sorry for that.

In the above table Bugs@10% column is created by me to draw a output in excel to post for this question. In reality, that column not exist so the dates from may'24 to dec'24 not available in the table. 1st Question is how to populate these dates to draw complete year chart and 2nd part is how can i make the year selection as filter and create chart accordingly. 

[lbendlin]

Your data model must contain a Calendar table. Use the dates from there. Add the year filter from there as well.

[manojk_pbi]

@lbendlin , I did try but didn't work properly. Will give a try again and check in case of any issues in the model.

[lbendlin · Accepted solution]

See attached.  I made your burndown goal flexible.