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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
YDG
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.

YDG_0-1718317007831.png

 

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.

YDG_1-1718317022223.png

 

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
Anonymous
Not applicable

Hi @YDG ,

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

1.Add an index column.

vyifanwmsft_0-1718331518910.png

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:

vyifanwmsft_1-1718331756839.png

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.

YDG
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!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!