Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
How can I show the dates before the slicer date selection? The Slicer date range is 7/1/2023-7/20/2023.
I want to show the arrival dates before 7/1,
For example,
Customer A arrived on 6/29/2023 and departed on 7/06/2023. I want to show this record as his departure is 7/15, which still falls in our date range(7/1/2023-7/20/2023).
Can anybody help me with this please?
Solved! Go to Solution.
Hi @Suryateza ,
Please try below steps:
1. below is my test table
Table:
Date:
Date = CALENDAR(DATEVALUE("2023/07/01"),DATEVALUE("2023/07/20"))
2. create a measure with below dax formula
Measure =
VAR min_date =
MIN ( 'Date'[Date] )
VAR max_date =
MAX ( 'Date'[Date] )
VAR cur_arr =
SELECTEDVALUE ( 'Table'[Arrived] )
VAR cur_dep =
SELECTEDVALUE ( 'Table'[Departed] )
VAR result =
SWITCH (
TRUE (),
cur_arr >= min_date
&& cur_dep <= max_date, 1,
cur_arr < min_date
&& cur_dep >= min_date
&& cur_dep <= max_date, 1
)
RETURN
result
3. add a table visual with Table fields, add a slicer visual with Date table, add measure to table visual filter pane and apply
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Suryateza ,
Please try below steps:
1. below is my test table
Table:
Date:
Date = CALENDAR(DATEVALUE("2023/07/01"),DATEVALUE("2023/07/20"))
2. create a measure with below dax formula
Measure =
VAR min_date =
MIN ( 'Date'[Date] )
VAR max_date =
MAX ( 'Date'[Date] )
VAR cur_arr =
SELECTEDVALUE ( 'Table'[Arrived] )
VAR cur_dep =
SELECTEDVALUE ( 'Table'[Departed] )
VAR result =
SWITCH (
TRUE (),
cur_arr >= min_date
&& cur_dep <= max_date, 1,
cur_arr < min_date
&& cur_dep >= min_date
&& cur_dep <= max_date, 1
)
RETURN
result
3. add a table visual with Table fields, add a slicer visual with Date table, add measure to table visual filter pane and apply
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You can Try a different approach creating a new Table using the below DAX Expression,
DateRange = CALENDAR(MINA(Sampletable2[Arrived On]),MAXA(Sampletable2[Departed ON]))
You have to create the relationship between the date range table and your required table.
Please refer to the Below Screenshot,
Thanks!
Inogic Professional Services Division
Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!
Drop an email at crm@inogic.com
Services: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
View solution in original post
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |