Forum Discussion
Comparing 2 dates not working
FStettler As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/
Once you set a relationship between data and transaction table, you can simply add previous day measure as below
Previous Day =
CALCULATE ( SUM ( Table[Amount] ), PREVIOUSDAY ( DateTable[Date] ) )
or
Previous Day =
CALCULATE ( SUM ( Table[Amount] ), DATEADD ( DateTable[Date] ), -1, DAY )
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
Hi parry2k thanks for your swift answer.
I've tried all that. I have my calendar table set up as date table, and the relationship made.
PREVIOUSDAY won't work as I don't need the immediate previous date like in a continuous calendar table. I need the previous date WITH DATA. In my example, some data was collected on 17/02/2020 and the next day where data was collected was 19/02/2020, and then the next one was 11/06/2020. This doesn't follow a pattern.
PREVIOUSDAY and DATEADD in your example are related to the immediate previous date, which is most likely to be blank in my model.
Thanks again.
Facundo
FStettler ok in that case try this
Prev Day = VAR __prevDate = CALCULATE ( MAX ( TransactionTable[Date] ), FILTER ( ALL ( DateTable[Date] ), DateTable[Date] < MAX ( DateTabble[Date] ) ) ) RETURN CALCULATE ( SUM ( Table[Amount] ), DateTable[Date] = __prevDate )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
FStettler and one thing when you are visualizing your data, make sure you are taking date from date dimension table, not from the transaction table as that date will be driving the logic.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
