Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
Please download attachment file for more details.
Best Regards,
Angelia
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
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |