Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
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.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |