Forum Discussion

Giada90's avatar
Giada90
Helper IV
3 years ago
Solved

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...
Need Help
  • Samarth_18's avatar
    Samarth_18
    3 years ago

    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
            )
        )
    )
Samarth_18's avatar
Samarth_18
Community Champion
3 years ago

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's avatar
Giada90
Helper IV
3 years ago

thanks!