cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Community Champion

@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
Community Champion

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

Community Champion

@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!