Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculate Function Does Not Change Filter Context

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.

Need Help
  • danextian's avatar
    danextian
    1 year ago

    hI Anonymous 

     

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

     

2 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    hI Anonymous 

     

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