Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
gsed99
Helper III
Helper III

Using Date table to impact formula but not filter data

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.

 

gsed99_0-1654119284488.png

 

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi  @gsed99 ,

I created some data:

2018-2021-ARR:

vyangliumsft_0-1654483571356.png

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

vyangliumsft_1-1654483571357.png

3. Result:

vyangliumsft_2-1654483571361.png

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

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Hi  @gsed99 ,

I created some data:

2018-2021-ARR:

vyangliumsft_0-1654483571356.png

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

vyangliumsft_1-1654483571357.png

3. Result:

vyangliumsft_2-1654483571361.png

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.