Calculate Function Does Not Change Filter Context

[Moxam]

Hello everyone,

I have a data model that has two tables: Calendar, which a non-standard date table, and Transactions. They are joined by a column called period. The period takes the form of YYYYMM, e.g., 202410. Each row in the Calendar table represents a month. Another column inside Calendar also provides the first day of the month, so the dates are non-contiguous, which rules out the time intelligence functions.

Sales are calculated in a fairly standard way:

Total Sales = CALCULATE(SUM('Transaction'[Sales Amt])).

What I want is to find the sales amount last year as at the same month. When I try:

CALCULATE([Total Sales], Calendar[Period] - 100)

a blank is returned. The minus 100 is supposed to produce: 202310. I have also tried:

CALCULATE([Total Sales], Calendar[Period] = MAX(Calendar[Period]) - 100)

and the issue persists.

Any help would be greatly appreciated.

[Ashish_Mathur]

Hi,

Why in the first place do you have a non-standard date table?

[danextian · Accepted solution]

hI @Moxam 

If you have a start of month in your calendar table, you should still be able to use SAMEPERIODLASTYEAR or you can use EDATE on the current row start of month to go back to x perios prior.

12 Periods Back = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'CalendarTable' ),
        CalendarTable[Start Of Month]
            = EDATE ( MAX ( CalendarTable[Start Of Month] ), -12 )
    )
)

12 Periods Back SAMEPERIODLASTYEAR =
CALCULATE (
    SUM ( 'Table'[Value] ),
    SAMEPERIODLASTYEAR ( CalendarTable[Start Of Month] ),
    REMOVEFILTERS ( CalendarTable )
)