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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlexJ01
Helper I
Helper I

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
v-rzhou-msft
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
v-rzhou-msft
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?

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
Resident Rockstar
Resident Rockstar

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.