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
RobertSlattery
Resolver III
Resolver III

Calculate Opening Balance of Selected Range

 

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?

 

1 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 resultexpected result
Please download attachment file for more details.

Best Regards,
Angelia

View solution in original post

4 REPLIES 4
v-huizhn-msft
Employee
Employee

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

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

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 resultexpected result
Please download attachment file for more details.

Best Regards,
Angelia

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. Smiley Sad

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.