cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

## static and dynamic countdown with current and future dates

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.

1 ACCEPTED SOLUTION
Super User

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.

Super User

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.