cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## How to determine the contract is active or not use start and end date?

Hi everyone, I am doing an ACV analysis and want to mimic what we have in Excel.

We have a table which is the contract dataset.

And in Excel, we have a formula like below. By comparing to Start and End date, we determine if the contract is active in a specific month. If it's active, it has the ACV in that month.

In power BI, if I have the Contract Database and A Date table by month, how can I calculate if the contract is active in a specific month and sum up the ACV?

And if anyone is doing an ACV analysis in Power BI, do you have any suggestion?

2 REPLIES 2
Community Support

Hi @YDG ,

I created a sample data to solve your problem, you can follow the steps below:

2.Add new measure for each date, here is an example for 2023/1/31:

``````2023/1/31 =
VAR _start_date =
SELECTEDVALUE ( 'Contract Database'[Start Date] )
VAR _end_date =
SELECTEDVALUE ( 'Contract Database'[End Date] )
VAR _date_by_month =
CALCULATE (
MAX ( 'Date Table'[Date] ),
FILTER ( 'Date Table', 'Date Table'[Index] = 1 )
)
VAR _acv =
IF ( _start_date < _date_by_month && _end_date > _date_by_month, 1, 0 )
RETURN
IF ( _acv = 1, SUM ( 'Contract Database'[ACV] ), 0 )
``````

``Total = [2023/1/31]+[2023/2/28]+[2023/3/31]+[2023/4/30]+[2023/5/31]+[2023/6/30]+[2023/7/31]``

Final output:

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

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

Frequent Visitor

Thanks Ada for your solution! this does give me the same result as of the Excel formula. My only concern is that the real dataset contains multiple years' data from 2019 to now and we will keep adding new contracts. Instead of create one measure for each month, is there a way to be able to see the ACV value for any given month if the contract is active? Thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.