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

[mikemi]

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

[v-yuezhe-msft]

@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

[mikemi]

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!

[v-yuezhe-msft]

@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

[mikemi]

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]

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:

[mikemi]

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

[v-yuezhe-msft · 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