Multiple Curves from different date

[Mike_Mace]

My spreadsheet has 3 dates per deliverable. Planned date, date to Recovery, Actual received date

If I plot each running curve against its own set of dates date all three graphs are ok on an individual level. But I need to plot all 3 running curves on the same graph.

That means I can only use one set of dates on the X-axis. 
How can I show all curves on one graph? If I use the date from either of the 3 on the X axis, the running data from the other two gets distorted when plotted against the date set of the first.

Thanks

[v-kelly-msft]

Hi @Mike_Mace ，

Which graph are you using?I tested with line chart and it works fine.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Mike_Mace]

Hi @v-kelly-msft 

Maybe my first post didn't have enough info to show the full picture and I just uploaded more info and snips on my last post.

Yes I'm using line chart. Can you have a look at the new info please

I'm able to join a Team call if that works. I've been trying to solve this for a while now.

[Brycen123]

@v-kelly-msft 

Hello,

Any chance this has been resolved? And if so, can you please provide any help or a link to another article that answers the same issue? I am running into this problem and it would be great to know if there was a solution for it. 

Thanks

[amitchandak]

@Mike_Mace 

You can connect all three dates to one common date dimension. There will one active and 2 inactive relations. You can activate relation using userelation in the formula

Refer the usages : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Mike_Mace]

Hi @v-kelly-msft & @amitchandak 

I have created a Date based on Calendar and marked it as the date in model view.

I am not able to reach my goals yet 😕

Steps I take:

1. Create Running Totals for each of the 3x values, Planned, Actual, and Recovery. For the Running Totals I use quick measure calculation; Sum of Planned at Base value and Planned Date at Field
2. Create Date measure and mark it as the date in model view.
3. Create Relationships between Date and the date set from each value, Planned Date, Actual Date, Recovery Date (Forecast)
4. Plot a single line graph with Date (calendar) on Axis and the Running totals to have all three curves against the same date set

Problem is, when I plot Running (cumulative) against Date (calendar) the Running Planned do not show as cumulative anymore, they show as Planned. Showing in snips.

Snip:

1. Planned info: Table showing Planned date, Planned (per date), Running Totals for Planned
2. Date alignment: Table shows Planned date, Planned (per date), Date (calendar) aligning neatly with Planned date. This is available only after I create the relationship.
3. Running info against date misalignment: When I plot Running Total against Date (calendar) the Running totals are actually not running anymore. They go back to Planned (per date).
4. All 3x Running curves: Each Running column plotted against each own data set. This is what the curves need to look like. These are the curves that I am trying to have on one graph
5. Planned & Actual Running against Planned Date: This is an experiment. I created a relationship between planned dates and actual dates. Planned curve and Actual curve as per Snip 4. show properly when plotted against their own data sets. Adding Actuals on Planned Curve with Planned dates, shows distorted as you can see. Not the 1, 3, 4 running total. It shows a 2 and 1 In decline.
6. Raw data: What the data looks like imported from Excel. For every date, a second column calculates 1, or 0 if no date. This is the data I use to calculate the Running Totals.

I am wondering if what I am doing wrong is in calculating the Running Totals. I use the date set of each value. Since I create the relationship between Date (calendar) and the 3x date sets, why does data get distorted on snip 3?

1. Planned info2. Date alignment3. Running info against date misalignment4. All 3x Running curves5. Planned & Actual Running against Planned Date6. Raw data

[v-kelly-msft]

Hi @Mike_Mace ，

It is  because that you have created a calendar table that linked with 3 differenct date columns in another table,during data filtering ,some data is missing, can you try my way to put the 3 date columns in X-axis?

If it doesnt work,let me know.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Mike_Mace]

Hi @v-kelly-msft 

Thanks for the help to drive this to completion.

Just a note on the snip you send which shows 1 Value, for the sake of clarity, my aim is to get 3 separate curves on the same graph, 3 separate Values.

Sending snips of new attempts:

7. Test 1: I put all three date-sets on the Axis and all three running-totals in the values on the first graph.

Planned-running shows properly.

Actual-running and Recovery-running are distorted. They show the same value, overlapping, and are in decline.

Actual-running and Recovery-running should have the shape of the curves in the two graphs below.

8. Test 2: Same as Test 1 but I brought Forecast Date _for Recovery) at the top of the list of the date-sets on Axis. This time the Recovery-running shows accurately and Planned and Actual show distorted.

It appears that that the first date-set on Axis acts like the dominant set for the axis and the other two are dormant.

This is when it got me thinking it would be best to use Date-Calendar for the Axis and link the other 3 date-sets to it. But when I do that the Running-totals do not show as running-totals anymore

9. Test 3 shows just that.

Also refer to snip 3. Running info against date misalignment

10. Relationships: This shows the relationships I created

Date Calendar to Planned Date,

to Actual Date,

to Forecast Date

7. Test 18. Test 29. Test 310. Relationships