Forum Discussion
aloosh89
Helper I
Helper IChecking if difference between two dates is in a certain quarter
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 peri...
Hi aloosh89
If you need this flag as a "static" flag you can use the following dax measure :Flag =varstartdate_check= if(QUARTER(max('fact'[start date]))=1 && YEAR(max('fact'[Start Date]))=2023,1,0)varenddatdate_check= if(QUARTER(max('fact'[end date]))=1 && YEAR(max('fact'[End Date]))=2023,1,0)returnif (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 =varstartdate_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)varenddatdate_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)returnif (startdate_check=1 || enddatdate_check=1,TRUE(),FALSE())If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
aloosh89Helper I
Helper IHello,
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 |