Forum Discussion
Value for a previous date
hey all i have this issue where i created a column for 24 months prior to current month, called 24 months prior, i want to create a column called previous_open, that will give me the value of the ope...
I would use VAR (variables) and CALCULATE. For each line of the table I am storing the current date and the date corresponding to 24 months ago (PPYearMonth). Then I create a SUM of all the rows where date = PPYearMonth. There will only be one row, hence the SUM matches the value you want. Please notice that my date format is dd/mm/yyyy
Value -24M =
VAR ThisYearMonth = 'FACT'[Date]
VAR PPYearMonth = DATEADD('FACT'[Date], -24, MONTH)
RETURN
CALCULATE(
SUM('FACT'[Value]),
'FACT'[Date] = PPYearMonth
)
Remark: if you are building this for a bigger business challenge, I would recommend you to 1) create a calendar table and mark it as date table 2) create measures to leverage the timeintelligence functions which give you a lot more flexibility
Cheers!
very close, i added a all() statement and it worked
What was the purpose of the ALL() statement? It could be a good learning opportunity for me too