Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tcburge3
Helper I
Helper I

How to show 3 different date columns as 3 separate lines on the same date chart

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.

tcburge3_0-1654192130740.png

 

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!

2 ACCEPTED SOLUTIONS

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.

sevenhills_0-1654216475906.png

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:

sevenhills_1-1654216535087.png

 

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
Community Support
Community Support

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()))

veasonfmsft_0-1654762387770.png

Best Regards,
Community Support Team _ Eason

View solution in original post

6 REPLIES 6
v-easonf-msft
Community Support
Community Support

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()))

veasonfmsft_0-1654762387770.png

Best Regards,
Community Support Team _ Eason

tcburge3
Helper I
Helper I

My apologies, the table didnt post very well. Here is a picture below that shows the table a bit more clearly.

tcburge3_0-1654192601625.png
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.

sevenhills_0-1654216475906.png

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:

sevenhills_1-1654216535087.png

 

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!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.