Forum Discussion
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 | 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
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
6 Replies
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.
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)
Further.
This is the DAX formula I am using, but it returns blanks:
Prev Year Tot Prod = CALCULATE ( SUM( TABLE [Production]), SAMEPERIODLASTYEAR([Date]))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?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.
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
