YTD Calculation

[Himanshu_1306]

Hi All,

Please help me in correcting my YTD calculation as its giving me blank value since starting of the new year.

Previous Calculation Used ( It was working corretly till 31st Dec):

YTD ACV =

IF (

    MONTH ( TODAY () ) = 1,

    CALCULATE (

        [ACV],

        FILTER (

            ACV,

            YEAR ( ACV[CLOSE_DATE] )

                = YEAR ( TODAY () )

                && MONTH ( ACV[CLOSE_DATE] )

                    <= MONTH ( TODAY () )-1

        )

    ),

    CALCULATE (

        [ACV],

        FILTER (

            ACV,

            YEAR ( ACV[CLOSE_DATE] ) = YEAR ( TODAY () )

                && MONTH ( ACV[CLOSE_DATE] )

                    <= MONTH ( TODAY () ) -1

        )

    )

)

[Anonymous]

Hi,

could you please state the desired result?

Are you looking for data in the current year? Data in the last 12 months from the current date?

[Himanshu_1306]

Yes I want data in the last 12 months from current month. So my YTD values will show data till Dec'22.

My calculation was working completely fine till last week, not sure where the problem is in the calculation that its showing blank now.

[Anonymous · Accepted solution]

Try this:

CALCULATE(
    SUM(FactsTable[Data]),
    DATESINPERIOD('Date'[DateKey], TODAY(), -12, MONTH)
)

[Himanshu_1306]

Thanks for the help.

It worked after few corrections !!!

YTD ACV =

IF (

    MONTH ( TODAY () ) = 1,

CALCULATE(

    [ACV],

    DATESINPERIOD(ACV[CLOSE_DATE], TODAY(), -12, MONTH)

    ),

    CALCULATE (

        [ACV],

        FILTER (

            ACV,

            YEAR ( ACV[CLOSE_DATE] ) = YEAR ( TODAY () )

                && MONTH ( ACV[CLOSE_DATE] )

                    <= MONTH ( TODAY () ) -1

        )

    )

)