dynamic date selecting a date filter

[Giada90]

Hi, 

I need to create a text with dynamic date based on selected filter date.

For example if I select 2023 I need to see

-starts before  01/01/2024 and end after 01/01/2025.

-starts before 01/01/2024 and end on 31/12/2024.

-starts and end in 2024.

-starts in 2024 and ends after 01/01/2025.

if I select 2022 I need to see the same but with one year before etc.....

how can I do? which formula I need to use to have this date?

thanks

[Deku · Accepted solution]

var Year = Selectedvalue( table[year] ) + 1
var startofYear = DATE( Year, 1, 1 )
var startofFollowingYear = DATE( Year + 1, 1, 1 )
var endofYear = startofFollowingYear - 1
return
"-starts before " & startofYear & " and end after " & startofFollowingYear & "." & CHAR(10) &
"-starts before " & startofYear & " and end on " & endofYear & "." & CHAR(10) &
"starts and end in " & Year & "." & CHAR(10) &
"starts in " & Year & "and ends after " & startofFollowingYear & "."

You might want to wrap the dates in FORMAT( , "dd/mm/yyyy") to format them as you like

[Giada90]

thanks!

[Giada90]

@Deku thanks, but if I try the formula I get error from the second var, the first var works correctly

[Deku]

updated from 01 to 1 let me know if that was it

[Giada90]

@Deku it doesn't take the first variable year inside the second variable startofyear

[Deku]

Assume you year is a string you will need to wrap the selectedvalue in int() to convert the text to a number

[Anonymous]

Hi @Giada90 ,

Sorry, I don't really understand what you mean, can you provide more examples? Are you trying to get the following text based on the selected year, or are you filtering the data for the following date range?

Best regards,

Rimmon Li

[Giada90]

@Anonymous hi, I'm trying to get the following text based on the selected year, thanks

[Deku]

Is this the kind of thing you are looking to see?

https://evaluationcontext.github.io/posts/IslandsAndGaps/