cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlexJ01
Regular Visitor

How to get two different Cumulative Graphs to Display in one chart

Hi all,

 

I'm trying to get two cummulative line graphs displayed within one chart, but each line has a different date source. 

The first line shows the planned date vs planned data and the second line should show the actual date vs actual data. Currently, I can only get one line to accurately show the cummulative data over the time period. I think this may need screenshots to explain what I'm trying to do.... example image.png

So essentially I want the dark blue line (cummulative actual data vs actual date) plotted on the same chart as the light blue line (cummulative planned data vs planned date). 

 

From my playing around it seems the issue is the axis field, as I can get each individual graph to display correctly depending on what field I put in there. 

 

Thanks in advance!

 

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @AlexJ01 ,

 

I suggest you to create a DimDate table and then build relationships between DimDate[Date] and Actual/Planned [Date].

 

DimDate = CALENDAR(MIN(Planned[Planned Received Date]),MAX(Planned[Planned Received Date])) 

 

Relationship:

1.png

Measures:

 

Actual runnint total = CALCULATE(SUM(Acutal[Value]),FILTER(ALL('DimDate'),'DimDate'[Date]<=MAX('DimDate'[Date])))
Planned running total = CALCULATE(SUM(Planned[Value]),FILTER(ALL('DimDate'),'DimDate'[Date]<=MAX('DimDate'[Date])))

 

Result is as below.

1.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
RicoZhou
Community Support
Community Support

Hi @AlexJ01 ,

 

I suggest you to create a DimDate table and then build relationships between DimDate[Date] and Actual/Planned [Date].

 

DimDate = CALENDAR(MIN(Planned[Planned Received Date]),MAX(Planned[Planned Received Date])) 

 

Relationship:

1.png

Measures:

 

Actual runnint total = CALCULATE(SUM(Acutal[Value]),FILTER(ALL('DimDate'),'DimDate'[Date]<=MAX('DimDate'[Date])))
Planned running total = CALCULATE(SUM(Planned[Value]),FILTER(ALL('DimDate'),'DimDate'[Date]<=MAX('DimDate'[Date])))

 

Result is as below.

1.png

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Rico,

 

Thank you for your help. The issue I have is that I get the error bellow when I try to make a relationship between the 3 date columns (ie between planned date, DimDate and Actual Date). 

AlexJ01_0-1643297412337.png

Is there any reason for this/or solution?

RicoZhou
Community Support
Community Support

Hi @AlexJ01 ,

 

I think your actual data model in more complex than my sample.

The relationship in my sample is Actual[Date] —— DimDate[Date] and DimDate[Date] —— Planned[Date].

1.png

The Warning in your screenshot means that there should be active relationships as

DimDate[Date] —— Planned[Date] and Planned[Date] —— Actual[Date] in your data model. So Power BI doesn't support you to create an active relationship: Actual[Date] —— DimDate[Date]. Please check your data model. Or you can download my sample file as above and compare it with yours.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

StefanoGrimaldi
Super User
Super User

you have 2 option the scond being more of a trick:

1) create a custom visual to achevied this or see in the marketplace if someone have already done it and its avaible to general use or to buy it

2) overlap the 2 visual with the exact same axis configuration and range, them make the top visual tranparent to a certain degree that allows the second visual to be visible and that would overlap to a certain degree





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors