Forum Discussion

jfranco's avatar
jfranco
Frequent Visitor
2 years ago
Solved

DAX Calculated Column - Value from a same date previous year

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 ...
Need Help
  • jfranco's avatar
    jfranco
    2 years ago

    Thank you Anonymous 

    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

belvoir99's avatar
belvoir99
Resolver III
2 years ago
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's avatar
jfranco
Frequent Visitor
2 years ago

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)