Checking if difference between two dates is in a certain quarter

[aloosh89]

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

[Ahmedx]

I wrote 3 solutions for you

Checking if difference.pbix

[aloosh89]

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!

filter('table',  

    (('Table'[START_DATE].[QuarterNo]=3 && 'Table'[START_DATE].[Year]=2023) || ('Table'[END_DATE].[QuarterNo]=3 && 'Table'[END_DATE].[Year]=2023))

|| ('Table'[START_DATE]<DATE(2023, 07, 01) && 'Table'[END_DATE]>DATE(2023,09,30))))

[aloosh89]

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

[Ahmedx]

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

[aloosh89]

@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.

[Ritaf1983 · Accepted solution]

Hi @aloosh89 
If you need this flag as a "static" flag you can use the following dax measure :

Flag =

var

startdate_check= if(QUARTER(max('fact'[start date]))=1 && YEAR(max('fact'[Start Date]))=2023,1,0)

var

enddatdate_check= if(QUARTER(max('fact'[end date]))=1 && YEAR(max('fact'[End Date]))=2023,1,0)

return

if (startdate_check=1 || enddatdate_check=1,TRUE(),FALSE())

If you need this more dynamically then:
you can create a "quarters dictionary" disconnected  table like :

and use measure :

Flag_Dynamic =

var

startdate_check= if(QUARTER(max('fact'[start date]))=QUARTER(max('Quarters dictionary'[start date])) && YEAR(max('fact'[Start Date]))=year(max('Quarters dictionary'[start date])),1,0)

var

enddatdate_check= if(QUARTER(max('fact'[end date]))=QUARTER(max('Quarters dictionary'[end date])) && YEAR(max('fact'[End Date]))=year(max('Quarters dictionary'[start date])),1,0)

return

if (startdate_check=1 || enddatdate_check=1,TRUE(),FALSE())

Link to a sample file 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[aloosh89]

@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.