Losing my mind...can someone tell me what's wrong?

[rilzniak]

I'm trying to write a measure in Power Pivot for the current year through the last date I have data for (Jan. 17, 2025). I also want to do the same for the prior year (code below). The results are connected to a year slicer. I'm getting an error - The formula contains an invalid character '['. - on the line "SUM('Data'[Sales])," and I have no idea why. I've tried with and without the quotes but it doesn't resolve the issue. Any ideas?

=
VAR CurrentDate = MAX('Calendar'[Date])
VAR StartOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)

VAR PriorYearStartOfMonth = DATE(YEAR(CurrentDate) - 1, MONTH(CurrentDate), 1)
VAR PriorYearEndOfMonth = EOMONTH(PriorYearStartOfMonth, 0)

VAR IsFutureMonth = CurrentDate > [Last Sales Date]

RETURN
    IF(
        IsFutureMonth,
        -- Future month: Return full prior year's month sales
        CALCULATE(
            SUM('Data'[Sales]),
            FILTER(
                'Data',
                'Data'[Date] >= PriorYearStartOfMonth &&
                'Data'[Date] <= PriorYearEndOfMonth
            )
        ),
        -- Current month or earlier: Return MTD sales
        CALCULATE(
            SUM('Data'[Sales]),
            FILTER(
                'Data',
                'Data'[Date] >= StartOfMonth &&
                'Data'[Date] <= [Last Sales Date]
            )
        )
    )

 

[RoryA]

Remove the comments, and use a different name to StartOfMonth as that is a DAX function.

 

[rilzniak]

Remove the comments, and use a different name to StartOfMonth as that is a DAX function.

Thanks Rory. The formula works for years prior to 2025 but when I select the '2025' Year slicer, I get a blank. The purpose was for me to show 2025 actuals through the most recent date I have data for and also 2024 actuals through the same day and month.

Do you have any suggestions as to what I've done wrong?