Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
I want to create a formula that uses Contract Start Date and Contract End Date from my data and and feeds off a separate data table(s). The dates selected from the data table should not filter the actual date, only impact the formulas to a Y/N outcome. I want to set a Beginning of Period and End of Period in the Date Table, and ideally the beginning would feed off the end formulaically by giving a rolling 12 period (if I select 9/30/21 as EOP, then 10/1/20 is BOP), however there can be two date fields if thats easier. I want to be able to select BOP and EOP and have the formula in my data determine Y or N based off those so I can then filter on the Y/N in my data for analysis, as I want to analyze contracts whos dates fall between BOP and EOP as I change the range.
I have created two date tables (pic below) but can only get them to filter the actual data and I only want them to impact the formula so as I pick the dates the formulas change but the data displayed does not.
Example formulas:
IF(AND(BOP>=Contract Start Date,Date Table Date<=Contract End Date),""Y","N")
IF(AND(EOP>=Contract Start Date,Date Table Date<=Contract End Date),""Y","N")
Example expected result:
If I choose BOP=10/1/2020 and EOP=9/30/2021, then a contract with a start date of 5/1/2018 and end date of 04/30/21 should have Y for BOP, but N for EOP since 9/30/21 is outside the range of the start/end dates but 10/1/2020 is within. Meanwhile the data from the ARR table does not filter at all based on the dates selected from the date table.
Any help would be tremendously appreciated!!
@Greg_Deckler - you have helped me in the past and I know you are a wizard!
Solved! Go to Solution.
Hi @gsed99 ,
I created some data:
2018-2021-ARR:
Here are the steps you can follow:
1. Create measure.
Flag1 =
var _select=SELECTEDVALUE('Date Table'[Date])
var _conStart=CALCULATE(MAX('2018-2021-ARR'[Contract Start Date]),FILTER(ALL('2018-2021-ARR'),'2018-2021-ARR'[Date]=_select))
var _conEnd=CALCULATE(MAX('2018-2021-ARR'[Contract End Date]),FILTER(ALL('2018-2021-ARR'),'2018-2021-ARR'[Date]=_select))
var _bop=SELECTEDVALUE('Ending Date'[BOP])
var _Eop=SELECTEDVALUE('Ending Date'[EOP])
return
IF(
_bop>=_conStart&&_select<=_conEnd,"Yes","No")
Flag2 =
var _select=SELECTEDVALUE('Date Table'[Date])
var _conStart=CALCULATE(MAX('2018-2021-ARR'[Contract Start Date]),FILTER(ALL('2018-2021-ARR'),'2018-2021-ARR'[Date]=_select))
var _conEnd=CALCULATE(MAX('2018-2021-ARR'[Contract End Date]),FILTER(ALL('2018-2021-ARR'),'2018-2021-ARR'[Date]=_select))
var _bop=SELECTEDVALUE('Ending Date'[BOP])
var _Eop=SELECTEDVALUE('Ending Date'[EOP])
return
IF(
_Eop>=_conStart&&_select<=_conEnd,"Yes","No")
2. Put [Flag1] and [Flag2] in the corresponding Visual Filter, and set is = "Yes".
3. Result:
If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @gsed99 ,
I created some data:
2018-2021-ARR:
Here are the steps you can follow:
1. Create measure.
Flag1 =
var _select=SELECTEDVALUE('Date Table'[Date])
var _conStart=CALCULATE(MAX('2018-2021-ARR'[Contract Start Date]),FILTER(ALL('2018-2021-ARR'),'2018-2021-ARR'[Date]=_select))
var _conEnd=CALCULATE(MAX('2018-2021-ARR'[Contract End Date]),FILTER(ALL('2018-2021-ARR'),'2018-2021-ARR'[Date]=_select))
var _bop=SELECTEDVALUE('Ending Date'[BOP])
var _Eop=SELECTEDVALUE('Ending Date'[EOP])
return
IF(
_bop>=_conStart&&_select<=_conEnd,"Yes","No")
Flag2 =
var _select=SELECTEDVALUE('Date Table'[Date])
var _conStart=CALCULATE(MAX('2018-2021-ARR'[Contract Start Date]),FILTER(ALL('2018-2021-ARR'),'2018-2021-ARR'[Date]=_select))
var _conEnd=CALCULATE(MAX('2018-2021-ARR'[Contract End Date]),FILTER(ALL('2018-2021-ARR'),'2018-2021-ARR'[Date]=_select))
var _bop=SELECTEDVALUE('Ending Date'[BOP])
var _Eop=SELECTEDVALUE('Ending Date'[EOP])
return
IF(
_Eop>=_conStart&&_select<=_conEnd,"Yes","No")
2. Put [Flag1] and [Flag2] in the corresponding Visual Filter, and set is = "Yes".
3. Result:
If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.