DAX Calculated Column - Value from a same date previous year

[jfranco]

Hello and Good Morning.

I have another question from the knowledge base.

I have the following table:

ID	Location	Date	Production
A	North	01/01/2024	10
B	North	01/01/2024	9
C	North	01/01/2024	11
A	South	01/01/2024	20
B	South	01/01/2024	18
C	South	01/01/2024	22

I'm trying to create a calculated column, that returns the Production Value for the same period (month) the previous year.

Example

for A : North : 01/01/2024  return the production for A : North : 01/01/2023 and so on

Thanks in advance

Roger

[v-jialongy-msft]

Hi @jfranco 

Please try the following dax:

PreviousYearProduction = 
CALCULATE(
    SUM('Table'[Production]),
    FILTER(
        ALL('Table'),
        'Table'[ID] = EARLIER('Table'[ID]) &&
        'Table'[Location] = EARLIER('Table'[Location]) &&
        'Table'[Date] = DATEADD('Table'[Date], -1, YEAR)
    )
)

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jfranco · Accepted solution]

Thank you @v-jialongy-msft 

Thank you!

Almost but not quite.

I ended up creating two columns

M = Month(Table[Date])

PY = Year(Table[Date])

and then using a variation of your DAX code :

CALCULATE (SUM([Production)]),   

FILTER(ALL(Table),

Table[ID]=EARLIER(Table[ID])

&& Table[Location] = EARLIER(Table[Location])

&& Month(Table[Date]) = Earlier(Table[M])

&& Year(Table[Date]) = EARLIER(Table[PY]) )))

Gracias

Roger

[jfranco]

Something else I've tried:

Added Two Columns:

LYM = MONTH (DATE)

LY = YEAR (DATE) -1

Then tried : 

CALCULATE (SUM([Production]),

MONTH([Date]) = [LYM],

year([Date]) = [LY]))

But still get blanks...any idea what I'm doing wrong?

[jfranco]

Further.

This is the DAX formula I am using, but it returns blanks:

Prev Year Tot Prod  =  CALCULATE ( SUM( TABLE [Production]), SAMEPERIODLASTYEAR([Date]))

[belvoir99]

LastYearDate = DATE(YEAR('Calendar'[Date])-1, MONTH('Calendar'[Date]), DAY('Calendar'[Date]))

in the above calculated column, 1 is subtracted from the Year.
see - https://learn.microsoft.com/en-us/dax/date-function-dax for more info.

the data type returned is a date/time where the time is midnight i.e. 00:00 - that should be fine for most purposes.

in the above example 'Calendar' is the name of my table - you need to replace that with yours. You don't need apostrophes unless it is a reserved word (like Calendar) or the table name has a space (etc) in the name.

[jfranco]

Thanks @belvoir99 

I appreciate the formula to get the calculation for the same date last year, but what I am trying to accomplish is Return the Production (or sales or any other variable) for ID = A (or B or C), and Location = North (or South) for  DATE last year (so if Jan 15 2024 then Jan 15 2023)