I only want to compare the visible months

[gennaro_19]

hj
My sales database runs from January 2024 to March 2025.
I like to compare the sales of the current year with those of the same period in the previous year.

I created my own measure and it works well at month level.

PY Sales = IF(

    ISBLANK(

        MAX('Sales'[Data])), BLANK(),

        CALCULATE(

            [CY Sales],

            SAMEPERIODLASTYEAR('Calendar'[Date])

        )

)

The measure does not work well at the yearly total level because it gives me the total of all months of the previous year.

Instead, I would like to have, at the total level, only the sum of the visible months, in this case, January to March 2025.

How do I modify my measure?

I enclose the file.
https://drive.google.com/file/d/19e9GHE8j6lzJwsNrbx_Zdj4PpEadoruT/view?usp=drive_link
Thank you

[Ashish_Mathur]

Hi,

Access denied message.  Check the download link.

[gennaro_19]

Hj @Ashish_Mathur ,
Thank you for your attention.
I shared the.pbix file on google drive.
I don't know why, but following the link shows a confusing window.
However, if you select the download icon, the next window is the classic file download window.
In the file you will find my old measure that does not work and the new one suggested by Duke that does work.
bye & thks

[Deku]

Think this should do it. Assumes your calendar table only have dates up to mar 25

PY Sales =

IF(
Not ISBLANK(MAX('Sales'[Data])),
CALCULATE(
[CY Sales],
DATEADD(

Values( calendar[date] )

), -12, month )
)
)

[gennaro_19]

Thank you very much @Deku for your solution, but it requires updating, extending, the calendar table every month when new sales data arrive.
I do not want to update the calendar table every month.
I would like a dynamic solution.
Sorry and thanks

[Deku · Accepted solution]

PY Sales =
IF(
   Not ISBLANK(MAX('Sales'[Data])),
   CALCULATE(
      [CY Sales],
      DATEADD(
         DATESBETWEEN( calandar[date], min(calandar[date], max(sales[date]))
      ), -12, month )
   )
)

[gennaro_19]

thank you very much
🙏👍👋