Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I am trying to use a FILTER command in a dax expression for my dataset in which each row has attributes 'Start Date' and 'End Date'. I want to be able to check, if any portion of time period between the Start and the End dates is within a specific quarter. Let's say Quarter 1 of the year 2023. I have given a sample table below of two instances of the data (the date columns are already coded as date data type). For this example table I gave, the first row would be 'True' meaning it is in Q1 2023 while the second one would be 'False' that it is in Q12023. I am looking for an expression that would evaluate to true or false so I can apply my filter in what I am trying to do. The last column i have in my example table is not part of the dataseet but is what I want the boolean check to achieve
Help would be apprciated and thanks in advance.
Ali
ID | Start Date | End Date | In Q1 2023? |
1 | 1-Nov-22 | 1-Feb-23 | True |
2 | 3-Dec-23 | 10-Dec-23 | False |
I managed to find the solution. Just had to simplify. I put the following boolean checks in my filter and got the result I need. Inside my calculate expression, i applied the below filter (for Q32023). Same logic for any quarter. Brackets may be off as I truncated some other parts of the filter expression, but the boolean logic is correct. Thanks for all your help!
Hello,
Sorry but I should have given more representative data. Consider the new row I added where neither the start date nor the end date fall in Q12023. Yet in the period between the start and the end, Q12023 occurs. I want to be able to detect 'TRUE' for that as well. I basically need to get true for any start/end date range in which Q12023 is spanned, whether for part of the quarter or the entire.
ID | Start Date | End Date | In Q1 2023? |
1 | 1-Nov-22 | 1-Feb-23 | Yes |
2 | 3-Dec-23 | 10-Dec-23 | No |
3 | 1-Nov-22 | 5-Jul-23 | yes |
pls try this
In Q1 2023? =
VAR __Q1 = CALENDAR(DATE(2023,1,1),DATE(2023,3,31))
RETURN
[Start Date] in __Q1 || [End Date] in __Q1
@Ahmedx thanks for sharing. I see you are using start date and end date as measures. For me they are columns in a table, hence when I use the code you provided I can't capture the dates and the code has an error. Additionally, please see my reply below where I have added one more row to the data to represent what I am trying to do better. Thanks for your help.
Hi @aloosh89
If you need this flag as a "static" flag you can use the following dax measure :
If you need this more dynamically then:
you can create a "quarters dictionary" disconnected table like :
and use measure :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
@Ritaf1983 thanks for providing this method. Could you please take a look at my reply below in the thread where I added a new row explaining what I want to capture for a case that wasn't covered in my original post? Thanks for your help.
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |