make a formula with a date dynamic

[Giada90]

Hi,

I need to make this formula dynamic based on today's date, any help?

Ricavi_BDG_FY 2 = IF(average(Aggregato[FY])=2022, CALCULATE([Ricavi_BDG],DATESBETWEEN('Date'[Date],Date(2022,1,1),DATE(2022,12,2)),ALL('Date'[Date])), IF(AVERAGE(Aggregato[FY])=2021, CALCULATE([Ricavi_BDG], DATESBETWEEN('Date'[Date],Date(2021,1,1),date(2021,12,2)),ALL('Date'[Date])),IF(AVERAGE(Aggregato[FY])=2020, CALCULATE([Ricavi_BDG], DATESBETWEEN('Date'[Date], date(2020,1,1),date(2020,12,2)),all('Date'[Date])),0)))

Thanks

[Samarth_18]

Hi @Giada90 ,

Could you help us to understand which date you wanted to replace with todays date?

[Giada90]

Hi @Samarth_18 , I mean current year

[Samarth_18 · Accepted solution]

@Giada90 , You can try this:-

Ricavi_BDG_FY 2 =
VAR _current_year =
    YEAR ( TODAY () )
RETURN
    IF (
        AVERAGE ( Aggregato[FY] ) = _current_year,
        CALCULATE (
            [Ricavi_BDG],
            DATESBETWEEN (
                'Date'[Date],
                DATE ( _current_year, 1, 1 ),
                DATE ( _current_year, 12, 2 )
            ),
            ALL ( 'Date'[Date] )
        ),
        IF (
            AVERAGE ( Aggregato[FY] ) = _current_year - 1,
            CALCULATE (
                [Ricavi_BDG],
                DATESBETWEEN (
                    'Date'[Date],
                    DATE ( ( _current_year - 1 ), 1, 1 ),
                    DATE ( ( _current_year - 1 ), 12, 2 )
                ),
                ALL ( 'Date'[Date] )
            ),
            IF (
                AVERAGE ( Aggregato[FY] ) = _current_year - 2,
                CALCULATE (
                    [Ricavi_BDG],
                    DATESBETWEEN (
                        'Date'[Date],
                        DATE ( ( _current_year - 2 ), 1, 1 ),
                        DATE ( ( _current_year - 2 ), 12, 2 )
                    ),
                    ALL ( 'Date'[Date] )
                ),
                0
            )
        )
    )

[Giada90]

thanks!