Forum Discussion
Running Total Burndown
Hi folks, I am in need of help with a DAX problem I have been experiencing for over a week.
Problem Statement
I want to create a power-bi line-chart that is:
- A cumulative total, month-over-month.
- Show all the months of the year even if we don't have any data for future months.
- For future months, dont show a flat line, don't show any line. Show only up until current month basically.
- Filter for issues that have status of Done.
- Start from target number (i.e 40) and burn down to zero.
Data Source
I am using two data-sources, a calendar date data flow, and a JIRA export XLS.
The JIRA export has the following fields:
| Issue key | Issue id | Status | Summary | Assignee | Resolved | |
| PTS-11223 | 24965008 | Backlog | Title 1 | abc | ||
| PTS-11271 | 24965057 | In Progress Dev | title 2 | def | ||
| PTS-11260 | 24965046 | Done | title 3 | hij | 2/28/2025 | |
| PTS-11268 | 24965054 | Done | title 4 | kolk | 2/28/2025 |
Expected Output
What would my DAX measure be for this? What I have I know is wrong:
If anyone can help me, I would be super appreciative. Thanks!
18 Replies
thegreenoz can you paste data that is more readable than just #####, also what each calculation look like? You have two lines going on, what each number represents. You should provide sample data and output relevant to the sample to make it clear.
Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Hi. So for the ###% don't worry about that column unused. All I care about is the resolved column. As far as the lines ignore the other ones just one burn down line.
The data for JIRA looks like:
The shared calendar:
And what im getting as far as the UI
Hi,
from the screenshot you have provided here are my thoughts about it
- A cumulative total, month-over-month. - your line seems to be correct because i can only see Feb and march in resolved column
- Show all the months of the year even if we don't have any data for future months. - there are blank months so i can see you got that right as well
- For future months, don't show a flat line, don't show any line. Show only up until current month basically.- for this you will need to do the following
create a new dax calculated column in date table which it says if it part of this month or before
NewColumn = IF([Date] <= EOMONTH(TODAY(), 0), "Yes", "No") and filter the yes only (SS is a sample i tested not your data)
- Filter for issues that have status of Done.
you can simply drag and drop status column to filters and filter only done - Start from target number (i.e 40) and burn down to zero. - i can see a burning down in your sample line chart , so i feel like you got that right as well
Thanks I have added that. The one thing missing in my chart is that it should start at 40 and count downward. So I tried the following but getting a V-shaped chart.
thegreenoz ,
try this,
Burndown_Chart =
VAR MaxResolvedDate =
MAX ( 'JIRA_CICD'[Resolved] ) -- Get the latest resolved issue date
VAR TargetValue = 40 -- Set your starting target value
VAR IssuesResolved =
CALCULATE (
COUNT ( 'JIRA_CICD'[Issue key] ),
'JIRA_CICD'[Status] = "Done",
'JIRA_CICD'[Resolved] <= MAX ( 'Calendar'[Date] ) -- Include only up to current month
)
RETURN
IF (
MAX ( 'Calendar'[Date] ) <= MaxResolvedDate, -- Show data only up to resolved dates
TargetValue - IssuesResolved, -- Subtract cumulative resolved issues from target
BLANK () -- Hide future months
)Thanks for that. Unfortunately the line goes up after the current month again.
is it possible to proved a sample dataset with the issue you are having ?
Hi, yes the previous response I had shows the data set with the columns.
Hi thegreenoz ,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information and description to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi it didnt yet. Not sure what else I can provide I have responded to each person's suggestion.
Hi thegreenoz ,
Thank you for reaching out to the Microsoft Community Forum.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank youHi @thegreenoz ,
Thank you for reaching out to the Microsoft Community Forum.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank youHi @thegreenoz ,
Thank you for reaching out to the Microsoft Community Forum.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.
Thank you
