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,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
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
            )
        )
    )

4 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    3 years ago

    Hi Giada90 ,

     

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

      • 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
            )
        )
    )