Solved: Re: How to show 3 different date columns as 3 sepa...

tcburge3 · ‎06-02-2022

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!

sevenhills · ‎06-02-2022

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...

View solution in original post

v-easonf-msft · ‎06-09-2022

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