static and dynamic countdown with current and future dates

[dw700d]

I have created a calculated column to essentially countdown from the start date to the end date. I then create a card that the tracks days and months remaining until the end date is met . As each day goes by the days and months are reduced until they get to 0 which is when the end date is met.

Contribution	Date	End Date
contribution1	12/30/2019	7/31/2022
contribution2	6/29/2020	1/31/2023
contribution3	12/31/2020	7/31/2023
contribution1	12/30/2019	7/31/2024
contribution2	6/29/2020	1/31/2025
contribution3	12/31/2020	7/31/2025

Below is the dax that I use to calculate days remaining and months remaining

Months until funds must be spent = DATEDIFF(TODAY(),'QOZ to QOZB'[End Date],MONTH)

Days until funds must be spent = DATEDIFF(TODAY(),'QOZ to QOZB'[End Date],DAY)

The Problem is Contribution 3 occurs at a future date and using the dax above overstates the countdown from start date to end date.

The dax works for contributions 1 & 2 because the contribution has taken place already but it doesnt work for contribution 3 because 12/31/20 is a future date.

How can I keep the contribution 3 countdown static until 12/31/2020 while also allowing contributions 1 & 2 to remain dynamic since those countdowns are actively happening.

[lbendlin · Accepted solution]

Test if the start date is in the past. If yes, use your existing formula. If no, subtract the start date from the end date.

You can also short cut this by using MAX() with two parameters.

Months until funds must be spent :=  

DATEDIFF(MAX(TODAY(),'QOZ to QOZB'[Start Date]),
         MAX(TODAY(),'QOZ to QOZB'[End Date]),
         MONTH
)

Bonus: This will automatically yield zero when the end date has passed too.