topic Re: How to determine the contract is active or not use start and end date? in DAX Commands and Tips

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

YDG — Thu, 13 Jun 2024 22:21:09 GMT

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?

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

Anonymous — Fri, 14 Jun 2024 02:31:16 GMT

Hi YDG ,

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

1.Add an index column.

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 )

3.Add a total measure.

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

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

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

Best Regards,

Ada Wang

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

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

YDG — Fri, 14 Jun 2024 03:01:53 GMT

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!