Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need to create a Filter Measure to show the Item IDs which were opened during a specified date range (30 Jan 2021 - 28 Jan 2022).
Here's what I have so far but now I'm stuck...Please help!
Thanks!
Solved! Go to Solution.
Hi @Carrhill ,
If its static date range then you can try this:-
Opened_In_FY22 =
CALCULATE (
SUM ( 'Table'[Item ID] ),
FILTER (
'Table',
'Table'[Opened Date] >= DATE ( 2021, 01, 30 )
&& 'Table'[Opened Date] <= DATE ( 2022, 01, 28 )
)
)
or if its coming from date slicer then
Opened_In_FY22 =
var min_date = min(date_slicer[date])
var max_date = max(date_slicer[date])
CALCULATE (
SUM ( 'Table'[Item ID] ),
FILTER (
'Table',
'Table'[Opened Date] >= min_date
&& 'Table'[Opened Date] <= max_date
)
)
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Carrhill ,
If its static date range then you can try this:-
Opened_In_FY22 =
CALCULATE (
SUM ( 'Table'[Item ID] ),
FILTER (
'Table',
'Table'[Opened Date] >= DATE ( 2021, 01, 30 )
&& 'Table'[Opened Date] <= DATE ( 2022, 01, 28 )
)
)
or if its coming from date slicer then
Opened_In_FY22 =
var min_date = min(date_slicer[date])
var max_date = max(date_slicer[date])
CALCULATE (
SUM ( 'Table'[Item ID] ),
FILTER (
'Table',
'Table'[Opened Date] >= min_date
&& 'Table'[Opened Date] <= max_date
)
)
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Instead of FILTER use the DATESBETWEEN function. If you need specific dates you can just type them with some double quotes!
CALCULATE(SUM('Table'[Item ID], DATESBETWEEN('Table'[Date],MAX('Table'[Opened Date]),TODAY()))
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |