Forum Discussion

dw700d's avatar
dw700d
Post Patron
6 years ago
Solved

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.
 
 
 
Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    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.

     

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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.