Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am running into a problem in PowerBI where I am wanting to put 3 different lines on a line chart (With the x axis being the date range of the 3 columns), and add a summation of each of the date columns to the chart. Basically, I'm looking to create a burnup chart.
The chart would ideally look something like we see above. The data below is dummy data used as an example to show what my data looks like.
Example: The chart would show 3 lines, “Build Baseline” “Build Planned” and “Build Actual”
Deliverable Name | Build Baseline | Build Planned | Build Actual |
Build A | 4/15/2022 | 5/16/2022 |
|
Build B | 4/18/2022 | 5/14/2022 |
|
Build C | 4/19/2022 | 5/21/2022 | 5/20/2022 |
Build D | 4/20/2022 | 4/20/2022 | 4/20/2022 |
Build E | 4/23/2022 | 4/23/2022 | 4/27/2022 |
Build F | 4/24/2022 | 4/24/2022 | 4/19/2022 |
Expected Result from dummy data
Build Baseline: Sums up to 6 between the dates 4/15-4/24
Build Planned: Sums up to 6 between the dates 4/20-5/21
Build Actual: Sums up to 4 between 4/19-5/20
**The overall range of the X axis would be from 4/15-5/20 (or whatever the range of the data is, as I will have data that is due in the future)
Note:
I know ideally I would have one Date field in the X-Axis of the chart, and then could add some calculated columns for each of the 3 Date columns [Build Baseline Due], [Build Due (Updated)], [Build Actual] to put in the y axis. But im just not sure how I would go about that, especially putting them all on the same date graph?
I also read somewhere that I may want to create a separate “Date table” that could be used for this, but im not sure how that would work.
Any help would be greatly appreciated!
Solved! Go to Solution.
I read the post and your requirements need to be refined on your post.
You cannot have all three dates and show vertically the counts on each date, as they span much range. unless you are using quarter or year.
Tip: Create the data sample in excel and copy/paste, as it helps anyone to use in their Power BI.
What you said in the notes is the correct way to go ...
1. Create a date table using DAX or Power Query. Lot of articles online. (references added as reply)
2. Model: connect the three dates to the date column
As you can choose only one active relationship between Date table and your tx data , it is upto you chose while column you want it as active.
I created all three as inactive relationships, not sure which one you want to use as active.
3. You can create the measures as per your need
Actual count = calculate ( COUNTA('Table'[Build Actual]), USERELATIONSHIP('Table'[Build Actual],'Date'[Date]))
Baseline count = calculate ( COUNTA('Table'[Build Baseline]), USERELATIONSHIP('Table'[Build Baseline],'Date'[Date]))
Planned count = calculate ( COUNTA('Table'[Build Planned]), USERELATIONSHIP('Table'[Build Planned],'Date'[Date]))
Note: you only need countrows or count or count (...) for the active relationship column. The code assumes all three are inactive, FYI.
4. You can create visualizations like below:
Optional: I provided the date table by week - start and end dates, to the right as your reference.
Chart has week from the date table and used all three measures...
Hi, @tcburge3
If you have a calendar table, please try formula as below:
Please try formulas as below:
Build Actual count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Build Actual]<=MAX('Calendar'[Date])&&'Table'[Build Actual]<>BLANK()))
Build Baseline count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Build Baseline]<=MAX('Calendar'[Date])&&'Table'[Build Baseline]<>BLANK()))
Build Planned count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Build Planned]<=MAX('Calendar'[Date])&&'Table'[Build Planned]<>BLANK()))
Best Regards,
Community Support Team _ Eason
Hi, @tcburge3
If you have a calendar table, please try formula as below:
Please try formulas as below:
Build Actual count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Build Actual]<=MAX('Calendar'[Date])&&'Table'[Build Actual]<>BLANK()))
Build Baseline count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Build Baseline]<=MAX('Calendar'[Date])&&'Table'[Build Baseline]<>BLANK()))
Build Planned count = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Build Planned]<=MAX('Calendar'[Date])&&'Table'[Build Planned]<>BLANK()))
Best Regards,
Community Support Team _ Eason
My apologies, the table didnt post very well. Here is a picture below that shows the table a bit more clearly.
Thanks,
Tim
I read the post and your requirements need to be refined on your post.
You cannot have all three dates and show vertically the counts on each date, as they span much range. unless you are using quarter or year.
Tip: Create the data sample in excel and copy/paste, as it helps anyone to use in their Power BI.
What you said in the notes is the correct way to go ...
1. Create a date table using DAX or Power Query. Lot of articles online. (references added as reply)
2. Model: connect the three dates to the date column
As you can choose only one active relationship between Date table and your tx data , it is upto you chose while column you want it as active.
I created all three as inactive relationships, not sure which one you want to use as active.
3. You can create the measures as per your need
Actual count = calculate ( COUNTA('Table'[Build Actual]), USERELATIONSHIP('Table'[Build Actual],'Date'[Date]))
Baseline count = calculate ( COUNTA('Table'[Build Baseline]), USERELATIONSHIP('Table'[Build Baseline],'Date'[Date]))
Planned count = calculate ( COUNTA('Table'[Build Planned]), USERELATIONSHIP('Table'[Build Planned],'Date'[Date]))
Note: you only need countrows or count or count (...) for the active relationship column. The code assumes all three are inactive, FYI.
4. You can create visualizations like below:
Optional: I provided the date table by week - start and end dates, to the right as your reference.
Chart has week from the date table and used all three measures...
Thank you so much, this worked! I had to st all relations to "one-to-many" since my data had more dates than I provided in the sample data. Thank you again so much for your help
In reality, you will have 1-m with dates. I was using sample data, so it acted as 1-1 with dates.
Glad the solution worked!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |