cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@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
Moderator v-yuezhe-msft
Moderator

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

@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.
Highlighted
mikemi
Frequent Visitor

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

 

 

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!

Moderator v-yuezhe-msft
Moderator

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

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

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

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.pngGross 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

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

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

Moderator v-yuezhe-msft
Moderator

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

@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

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors