cancel
Showing results for
Did you mean:
Helper III

## make a formula with a date dynamic

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
1 ACCEPTED SOLUTION
Super User

@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
)
)
)
``````

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

4 REPLIES 4
Super User

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

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Helper III

Hi @Samarth_18 , I mean current year

Super User

@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
)
)
)
``````

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Helper III

thanks!