Skip to main content
cancel
Showing results for 
Search instead 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

Reply
dw700d
Post Patron
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.

 

 

ContributionDateEnd Date
contribution112/30/2019       7/31/2022  
contribution2       6/29/20201/31/2023
contribution312/31/20207/31/2023
contribution112/30/20197/31/2024
contribution2       6/29/20201/31/2025
contribution312/31/20207/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
lbendlin
Super User
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.

 

View solution in original post

1 REPLY 1
lbendlin
Super User
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.

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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