topic Re: Urgent Reverse Burndown Chart for Features in Quarter in DAX Commands and Tips

Urgent Reverse Burndown Chart for Features in Quarter

Anonymous — Thu, 25 Jul 2024 01:27:30 GMT

Hi Everyone,

For all the experts of Power BI community, please help me in this dax. I am trying to create a burndown chart for Distinct Features Projected Vs Distinct Features Planned. I have two separate datasets for each like below

Feature Projected Dateset:

Feature_Id End Date
1842179 05/14/2024
1730860 03/04/2024
1764030 03/01/2024
1757984 02/27/2024
1757947 03/05/2024
1757062 04/09/2024
1732445 03/04/2024
1872820 07/18/2024
1845193 07/18/2024
2162467 09/06/2024
2003900 07/18/2024
1960331 07/18/2024
1872804 09/06/2024
1867369 09/01/2024

Feature Planned Dataset:

FeatureId Target Date
2216545 08/08/2024
2118272 07/20/2024
2093351 09/30/2024
783008 09/30/2024
1913126 09/22/2024
1366800 08/17/2024
2329963 10/07/2024
2303984 09/11/2024
2303975 09/11/2024
2251162 09/11/2024
2093348 09/30/2024

I have date dimension table and has active relationship with both datasets above.

I Created dax but I am getting output as below in snap:

Planned = CALCULATE(DISTINCTCOUNT('Committed Features'[FeatureId]), FILTER(ALLSELECTED('Calendar') , MAX('Calendar'[Date]) <= 'Calendar'[Date]))

Projected = CALCULATE(DISTINCTCOUNT(Merge1[Feature_Id]), FILTER(ALLSELECTED('Calendar') , MAX('Calendar'[Date]) <= 'Calendar'[Date]))

I am expected to get output as Below:
Where count ends with zero and datapoints are showing good

Could anyone please help me out here. Not sure where I am going wrong.

Greg_Deckler
community15
community24

@v-kongfanf-msft

Re: Urgent Reverse Burndown Chart for Features in Quarter

Greg_Deckler — Wed, 24 Jul 2024 17:33:08 GMT

Anonymous I have a Burndown chart example from Chapter 8 of DAX Cookbook. You can download the PBIX for chapter 8 here: DAXCookbook/Ch8 at master · gdeckler/DAXCookbook (github.com)

Re: Urgent Reverse Burndown Chart for Features in Quarter

Anonymous — Thu, 25 Jul 2024 01:14:47 GMT

Thank you so much Greg_Deckler for the cookbook, that helped me really understanding the concpet. But unfortunately, I have a bit different scenario here. As pasted in the sanp of expected output, I am required to burndown the counts of Projected features and Planned features with respect to thier End date and Target Date. the scenario in cook book is for hours and burndown the chart on basis of diff in those dates. I tried to put that logic in my scenario but didnt work. Greg_Deckler request you to please help me with scenario above. Thanks Again

Re: Urgent Reverse Burndown Chart for Features in Quarter

Anonymous — Thu, 25 Jul 2024 07:48:47 GMT

can anyone urgenely please help with this. Please let me know if more info is required. In post above I have mentioned the datasets I use and given snap of actual and expected output results. Pleaes let me know if there is way aorund to acheive it
@v-kongfanf-msft

Re: Urgent Reverse Burndown Chart for Features in Quarter

BeaBF — Thu, 25 Jul 2024 08:59:25 GMT

Anonymous I don't quite understand what the problem is

Re: Urgent Reverse Burndown Chart for Features in Quarter

Anonymous — Thu, 25 Jul 2024 09:39:07 GMT

Thanks for your reply BeaBF , Its about how visual should be represented. Actually i have to create a burn down chart for Features Planned Vs Projected in Quarter. I am showing the Planned and Projected features in Quarter and calculating reverse burndown on basis of thier End Date and target date.

So in order to acheive that I created below dax for burndown

Planned = CALCULATE(DISTINCTCOUNT(Feature Planned features[FeatureId]), FILTER(ALLSELECTED('Calendar') , MAX('Calendar'[Date]) <= 'Calendar'[Date]))

Projected = CALCULATE(DISTINCTCOUNT(Feature projected Features[Feature_Id]), FILTER(ALLSELECTED('Calendar') , MAX('Calendar'[Date]) <= 'Calendar'[Date]))

But as you can see in the snaps I pasted below, the output is not as expected.
I have pasted few mock up data of two datasets above which I use for this i.e. Feature projected Features and Feature Planned features datasets

So Basically, I Take distinct count of FeatureID from both dataset and with above dax I am doing the burndown. But I am expecting the output as below as we can see the count goes to zero and the datapoints breaking accurately (Have highlighted it). While the result I get shows a continous linear count without any breaks and not counting it down to zero.
Expected output:

Output I got:

Can you please let me know is there any other way to do it.

Re: Urgent Reverse Burndown Chart for Features in Quarter

BeaBF — Thu, 25 Jul 2024 10:00:29 GMT

Anonymous in my opinion you have to adjust the DAX formulas to count down features over time.

Planned =
CALCULATE(
DISTINCTCOUNT('Feature Planned Features'[FeatureId]),
FILTER(
ALLSELECTED('Calendar'),
'Feature Planned Features'[Target Date] >= 'Calendar'[Date]
)
)

Projected =
CALCULATE(
DISTINCTCOUNT('Feature Projected Features'[Feature_Id]),
FILTER(
ALLSELECTED('Calendar'),
'Feature Projected Features'[End Date] >= 'Calendar'[Date]
)
)

BBF

Re: Urgent Reverse Burndown Chart for Features in Quarter

Anonymous — Thu, 25 Jul 2024 10:30:29 GMT

Thanks alot BeaBF , you are saviour. Just one thing. The visual got populated correctly but unfortunately count is still not going to zero😔. Can you please suggest why its not going to count zero. I did some tweks in dax to call the Feature ID Column from both tables. I used Max function to call those columns. Below is how I did.

Planned =
CALCULATE(
DISTINCTCOUNT('Feature Planned Features'[FeatureId]),
FILTER(
ALLSELECTED('Calendar'),
MAX('Feature Planned Features'[Target Date]) >= 'Calendar'[Date]
)
)

Projected =
CALCULATE(
DISTINCTCOUNT('Feature Projected Features'[Feature_Id]),
FILTER(
ALLSELECTED('Calendar'),
MAX('Feature Projected Features'[End Date]) >= 'Calendar'[Date]
)
)

Output:

Re: Urgent Reverse Burndown Chart for Features in Quarter

BeaBF — Thu, 25 Jul 2024 10:36:43 GMT

Anonymous only add "+0" at the end of the measures:

Planned =
CALCULATE(
DISTINCTCOUNT('Feature Planned Features'[FeatureId]),
FILTER(
ALLSELECTED('Calendar'),
'Feature Planned Features'[Target Date] >= 'Calendar'[Date]
)
) + 0

Projected =
CALCULATE(
DISTINCTCOUNT('Feature Projected Features'[Feature_Id]),
FILTER(
ALLSELECTED('Calendar'),
'Feature Projected Features'[End Date] >= 'Calendar'[Date]
)
) + 0

BBF

Re: Urgent Reverse Burndown Chart for Features in Quarter

BeaBF — Thu, 25 Jul 2024 10:39:22 GMT

Anonymous If it's ok please accept my answer as solution 🙂

BBF

Re: Urgent Reverse Burndown Chart for Features in Quarter

Anonymous — Thu, 25 Jul 2024 10:55:08 GMT

BeaBF I added the + 0 but it got Shrinked. We got the results just dealing with that count zero

Re: Urgent Reverse Burndown Chart for Features in Quarter

BeaBF — Thu, 25 Jul 2024 10:58:12 GMT

Anonymous try change the filter direction of the relationship between Date Table and fact table