Help with burndown chart / DAX referencing previous rows

[401evolr]

New to Power BI.

Trying to create burndown charts showing actual vs target progress on jobs.

Snapshot of a portion of my dataset in query editor:

There are 2266 total jobs to be done for the year. Divided by 12 months, that’s 189 jobs per month as the monthly target. The ideal burndown line for the year would be a straight line from 2266 down to zero (like the orange line in the below example):

Eventually I would plot the completed and remaining jobs just like this example, but for now I’m just stuck on creating this ideal burndown target line.

I’m not sure how to cleanly aggregate the data by month and create the target burndown line.

I’ve manually created another monthly data table, then used a measure to calculate the annual job count and monthly target

columns. However, for the monthly burndown, I used a very ugly nested if statement to get what I wanted (my excel brain at work):

I'm sure there's a much easier and more automated way to do this, but I don't know all the ins-and-outs of DAX yet. Any help would be much appreciated!

[Naveen_SV]

@AllisonKennedy  Hi Allison i am unable to open this link for creating dim date table.

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/ 

[AllisonKennedy]

@Naveen_SV I have since created my own blog you can try this link: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

[AllisonKennedy]

If you already have the annual count and monthly goal, you can use DAX DATESYTD to get a cumulative measure for the monthly goal as the YTD goal.

This will require having a DimDate table. See here's a way to create if you don't already have:

https://www.mattmasson.com/2014/02/creating-a-date-dimension-with-a-power-query-script/

Then, use the following DAX as THE MEASURE:

Objetivo ytD: CALCULATE(SUM(Table[MonthlyTarget]), DATESYTD(DimDate[Date]))

Then just use:

Burndown á MAX(Table[AnnualCount]) - [YTD Target]

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and so that the community knows that their problem has been solved.

If you have found this post useful, please give it to Kudos.

I work as an instructor and consultant for Microsoft 365, specializing in Power BI and Power Query.

https://sites.google.com/site/allisonkennedycv

[401evolr]

Thanks for the quick reply @AllisonKennedy 

I might not be following correctly. I have added the DimDate table and created the YTD Target as a MEASURE. Then, I created a new column with the BURNDOWN formula:

However, it is only returning the correct burndown for January.

[401evolr]

Actually I have it working now - I forgot to link the relationship with the DimDate table.