Calculate Opening Balance of Selected Range

[RobertSlattery]

Open Orders Month Opening 2 = CALCULATE(
	[Open Orders],
	FILTER(ALL('Dim Date'),
		'Dim Date'[Date] < DATE(2017,12,1)
	)
)

I have, for example, December and Jan selected in my visual (using a page filter) and I want to use the first visible date in a calculation.  In other words, I want to replace the above with

Open Orders Month Opening 2 = CALCULATE(
	[Open Orders],
	FILTER(ALL('Dim Date'),
		'Dim Date'[Date] < [firstDateVisible]
	)
)

I tried 

firstDateVisible = CALCULATE(
	FIRSTDATE('Dim Date'[Date]),
	ALLSELECTED('Dim Date'[Date])
)

and although it works in a Card, it causes the Order Opening measure to return blank.

How can I get the first selected date inside the measure?

[v-huizhn-msft]

Hi @RobertSlattery,

>>although it works in a Card, it causes the Order Opening measure to return blank.

It's confusing. You mean in a Card visual, Order Opening measure returns the correct result, while it returns uncorrectly in other visualization? Do you mind share you sample table/.pbix file for further analysis? Do hide your sensitive information before unloading it, or you can send it by private information.

Thanks,
Angelia

[RobertSlattery]

Thanks @v-huizhn-msft, I uploaded an example here

[v-huizhn-msft · Accepted solution]

Hi @RobertSlattery,

When you select data range in slicer, the 'Orders' also be filtered during 2017/12/1-2018/1/31, the table visual still affected by the filter context. So Order Opening measure to return blank. Please write the measure using Variable as follows.

Open Orders Month Opening_test = VAR first_date=[firstDateVisible]
RETURN CALCULATE(
	SUM(Orders[Open Orders]),
	FILTER(ALL('Dim Date'),
		'Dim Date'[DateRaw] < first_date
	),
    ALL(Orders)
)

You will get expected result.

expected result
Please download attachment file for more details.

Best Regards,
Angelia

[RobertSlattery]

The graph does not respond to the slicer and your Open Orders Month Opening_test measure breaks if you select a date hierarchy for the graph axis.