Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 open column, lets say for first row of date 7/1/2023, i want to put the open column value for 7/1/2021 into the row of 7/1/2023 date, so that i will have the open value for the current month and the open value for 24 months prior, can any one help me? thanks ahead of time.

 

 

Need Help

3 Replies

  • emanuele3004's avatar
    emanuele3004
    Regular Visitor
    3 years ago

    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!


     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      very close, i added a all() statement and it worked

      • emanuele3004's avatar
        emanuele3004
        Regular Visitor
        3 years ago

        What was the purpose of the ALL() statement? It could be a good learning opportunity for me too