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
mikemi
Frequent Visitor

Area chart: two running totals each with a different date column from same table

Hi,

 

I am attempting to create an area chart with two running total values (I created them as measures): Gross Running Total Closed Won, and Gross Running Total in Pipeline by Created Date. Each has a different date column in the same table: "Close Date" and "Created Date." Any suggestions on how to get them to show accurately on the same viz?

 

Below is a screenshot with Created Date as the x-axis. As you can see, the Gross Running Total Closed Won (green) is not appearing as a running total, and it cuts off in March (end of x-axis is present day).

ARR-Pipeline.png

 

1 ACCEPTED SOLUTION

@mikemi,

Just change the Gross running total in close date measure to the following:

Gross Running Total in Close Date = 
CALCULATE(
	SUM('Opportunity'[Gross]),
	FILTER(
		ALLSELECTED('Date'[DateKey]),
		ISONORAFTER('Date'[DateKey], MAX('Date'[DateKey]), DESC)
	),
    USERELATIONSHIP('Opportunity'[Close Date],'Date'[DateKey])
)



Regards,
Lydia

Community Support Team _ Lydia Zhang
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

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

@mikemi,

I would recommend you create a calendar table, then create relationship between date field of the calendar table and the two dates(created date, closed date) in your original table, and re-create the two measures using date field of calendar table.

After that, drag date field of calendar table to x-axis in Area chart. If you have questions about DAX, please share sample data of your table and post expected result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Thanks @v-yuezhe-msft

 

 

I did create a calendar table and a relationship between DateKey (Date) and Opportunity (Created Date). I am unable to create a relationship between DateKey (Date) and Opportunity (Closed Date) because there's already an active relationship with Created Date.

 

Here is "Gross Running Total in Pipeline by Created Date":

 

Gross Running Total in Pipeline by Created Date = 
CALCULATE(
	SUM('Opportunity'[Gross]),
	FILTER(
		ALLSELECTED('Opportunity'[Created Date]),
		ISONORAFTER('Opportunity'[Created Date], MAX('Opportunity'[Created Date]), DESC)
	)
)

 

Here is the DAX for Close Date, which references another quick measure I created (Gross Running Total in Close Date) because I wasn't sure how to filter out two values in the field "Opp" (would be great to cut out this step and filter those two values in a single measure):

Gross Running Total Closed Won = 
CALCULATE(
	[Gross Running Total in Close Date],
	'Opportunity'[Stage] IN { "Closed Won" }, 'Opportunity'[Stage] = { "Closed Won" }, 'Opportunity'[Opp] <> { "XYZ" }, 'Opportunity'[Opp] <> { "123" }
)

 

 

Here is the Gross Running Total in Close Date which I referenced above:

Gross Running Total in Close Date = 
CALCULATE(
	SUM('Opportunity'[Gross]),
	FILTER(
		ALLSELECTED('Opportunity'[Close Date]),
		ISONORAFTER('Opportunity'[Close Date], MAX('Opportunity'[Close Date]), DESC)
	)
)

Thank you!

@mikemi,

You can create an inaticve relationship between DateKey (Date) and Opportunity (Closed Date) , then create running total measure with  USERELATIONSHIP function which uses the inactive relationship. 

There is a similar thread for your reference.
https://community.powerbi.com/t5/Desktop/Multiple-Running-Total-Line-Chart/td-p/174837

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks a lot, @v-yuezhe-msft. I reviewed that thread and understand the logic. I created an active relationship with DateKey[Date] and Opportunity[Created Date]. That works fine in the chart:

Gross Running Total in Pipeline by Created Date using DateKey[Date]Gross Running Total in Pipeline by Created Date using DateKey[Date]

 

However, I am unsure how to integrate the USERELATIONSHIP DAX in my measure for inactive Opportunity[Close Date]. I changed the measure in the CALCULATE to be the active "Gross Running Total in Pipeline by Created Date" since both measures are essentially the same, except for "Gross Running Total Closed Won" contains extra filters.

 

Gross Running Total Closed Won = 
CALCULATE(
	[Gross Running Total in Pipeline by Created Date],
	'Opportunity'[Stage] IN { "Closed Won" }, 'Opportunity'[Stage] = { "Closed Won" }, 'Opportunity'[Opp] <> { "XYZ" }, 'Opportunity'[Opp] <> { "123" }
)
***USERELATIONSHIP inputted somewhere above?***

 

Here are the relationships:

Relationships.png

 

mikemi
Frequent Visitor

@v-yuezhe-msft- just following up in case you have any ideas. Thank you!

@mikemi,

Just change the Gross running total in close date measure to the following:

Gross Running Total in Close Date = 
CALCULATE(
	SUM('Opportunity'[Gross]),
	FILTER(
		ALLSELECTED('Date'[DateKey]),
		ISONORAFTER('Date'[DateKey], MAX('Date'[DateKey]), DESC)
	),
    USERELATIONSHIP('Opportunity'[Close Date],'Date'[DateKey])
)



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.