DAX formula to check validity between start and finish dates and sum up values

[seankeatingpb]

Good Day, looking for some assistance if possible with the below:

I have a PBI report which includes a slicer for clients, as well as a date range slicer (the date range slicer relates back to a date table). I would like to be able to display the valid Total Agreement Value for that client on my report (card format), depending on the date range selected on the slicer. Is there a way to do this in DAX? I expect it would need to look at dates in-between the Agreement Start Date and Agreement Finish Date, if there are multiple valid agreements it would also need to sum these up.

Thanks in advance. 

Sample Data Table below:

Client	Product	Agreement Start Date	Agreement Finish Date	Active?	Agreement Value
A	Hardware	01/02/2022	31/01/2025	Yes	£32,000
A	Hardware	01/02/2019	31/01/2022	No	£32,000
A	Software	01/02/2023	31/01/2025	Yes	£9,000
B	Hardware	05/04/2024	04/04/2025	Yes	£25,000
B	Hardware	05/04/2023	04/04/2024	No	£25,000
B	Software	05/04/2024	04/04/2026	Yes	£13,000
C	Hardware	08/10/2023	07/10/2024	No	£55,000
C	Hardware	08/10/2024	07/10/2025	Yes	£55,000
C	Software	10/03/2023	09/03/2024	No	£8,000
D	Hardware	10/03/2021	09/03/2023	No	£11,000
D	Hardware	10/03/2023	09/03/2025	Yes	£11,000
D	Software	10/03/2023	09/03/2026	Yes	£18,000

[Poojara_D12 · Accepted solution]

Hello @seankeatingpb 

To achieve this in Power BI, we need a DAX measure that will calculate the Total Agreement Value for a selected client within the date range defined by the Agreement Start Date and Agreement Finish Date. This measure will check if an agreement falls within the selected date range, and if so, it will sum up the agreement values for valid agreements.

Here’s a step-by-step approach to create this measure in DAX:

1. Ensure Your Data Model Is Set Up Correctly

Make sure:

• You have a Date Table in your model that is marked as a Date Table.
• The Agreement Start Date and Agreement Finish Date columns in your data table are correctly set up to relate to this Date Table.
• You have a Client slicer and a Date Range slicer (which should be set up using the Date Table).

2. Create a DAX Measure to Calculate the Total Agreement Value

Use the following DAX measure, which will dynamically calculate the total agreement value based on the selected client and date range:

Total Agreement Value = 
VAR SelectedStartDate = MIN('Date'[Date])
VAR SelectedEndDate = MAX('Date'[Date])

RETURN
    CALCULATE(
        SUM('Agreements'[Agreement Value]),
        'Agreements'[Agreement Start Date] <= SelectedEndDate,
        'Agreements'[Agreement Finish Date] >= SelectedStartDate,
        'Agreements'[Active?] = "Yes"
    )

3. Add the Measure to a Card Visual

• Place this measure in a Card Visual in your Power BI report.
• When you select a client in the Client slicer and a date range in the Date Range slicer, the card will display the total agreement value for active agreements within the specified date range.

This should now display the Total Agreement Value dynamically based on your selected client and date range.

Let me know if this solution works for your report!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

[seankeatingpb]

Thank you Poojara for your prompt response. My mistake; on my real report, the Active? column is actually in a TRUE/FALSE format rather than Yes/No. Any idea what adjustments i would need to make to the formula to accomodate this? If i change to 'Agreements'[Active?] =  "True" I  receive an error mesage.

Thanks again.