Year increment on date column when reaches end of year

[Justas4478]

Hi, I have this column with date that serves as end date for my calendar table.
I am trying to make it when it is day before the date in the table it would increment year of the date by adding one year to the date.

So when IRL date is 30/12/2025 it would change date in calendar to 31/12/2026 from 31/12/2025.

Anyone know how I can make it work?

Thanks

[MarkLaf · Accepted solution]

Per your convo with lbendlin, it sounds like your actual issue is controlling the filter/visiblity of your calendar at the report layer.

But, for what it's worth, I think something like this would accomplish your original requirements, just add as additional steps to your current table with end of year.

let
    <... previous transform steps ...>
    <Previous Step> = <...>,

    //Get today's date by grabbing LocalNow
    Today = DateTime.Date( DateTime.LocalNow() ),

    //Reference to provided EoY date, sub in end of  
    //this year in case of null or lagging EoY date
    EoY = List.Max( { 
        Table.First( <Previous Step> )[Date], 
        Date.EndOfYear( Today ) 
    } ),

    //Bump date up to next year if today is on or after EoY-1
    NewDate = 
    if Today >= EoY - #duration(1,0,0,0) 
        then #date( Date.Year( EoY ) + 1, 12, 31 ) 
        else EoY,
    
    //Put new date back into desired table structure
    ToTable = #table( type table [ Date = date ], { { NewDate } } )

in
    ToTable

[lbendlin]

Make your calendar table cover one more year by default. Then each year change that manually.  No point in automating this.

[Justas4478]

@lbendlin The thing is I only get data once per week and already same year on its own is large amount of empty dates in calendar.
Thats why I am only limiting it to one year in advance.
It as well would remove need of going every year to edit date.

[lbendlin]

same year on its own is large amount of empty dates in calendar.

365 rows is not "large amount of data".

[Justas4478]

@lbendlin Not in terms of rows but in terms of days that are in the date slider filter.

[lbendlin]

Add a visual filter that blocks out all days after TODAY()

[Justas4478]

@lbendlin How do I add such filter?

[lbendlin]

add a measure

show = if(max(Calendar[Date])>TODAY(),0,1)

add the measure as a filter to your visual and set it to "is 1"