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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors