Forum Discussion
Solved
Previous day/date values
Hi guys, please i need help. I am trying to calculate the daily growth rate and I used this formula to get what I have in the "previous" column:
previous =
CALCULATE(
[current], PREVIOUSDAY('public sales_reports'[sale_dt]))
However, if the day is not the previous one, it doesn't work. I tried using EARLIER() but it did not work either.
Is there anyway to go about this and make the empty columns that have true beside them to have values? Well, except the first column. Help please!
| sale_dt | Revenue | current | previous | |
| 27.09.2021 00:00 | -882.00 ₽ | -882,00 RUB | TRUE | |
| 15.12.2021 00:00 | -199.13 ₽ | -199,13 RUB | TRUE | |
| 16.12.2021 00:00 | -398.00 ₽ | -398,00 RUB | -199,13 RUB | FALSE |
| 20.12.2021 00:00 | -199.00 ₽ | -199,00 RUB | TRUE | |
| 21.12.2021 00:00 | -1297.00 ₽ | -1 297,00 RUB | -199,00 RUB | FALSE |
| 22.12.2021 00:00 | -647.00 ₽ | -647,00 RUB | -1 297,00 RUB | FALSE |
| 23.12.2021 00:00 | -249.00 ₽ | -249,00 RUB | -647,00 RUB | FALSE |
| 24.12.2021 00:00 | -1172.00 ₽ | -1 172,00 RUB | -249,00 RUB | FALSE |
| 25.12.2021 00:00 | -897.00 ₽ | -897,00 RUB | -1 172,00 RUB | FALSE |
| 26.12.2021 00:00 | -816.00 ₽ | -816,00 RUB | -897,00 RUB | FALSE |
| 27.12.2021 00:00 | -1052.02 ₽ | -1 052,02 RUB | -816,00 RUB | FALSE |
Thanks in advance!
Hi,
In case there is no calendar table, please try like the below and the attached pbix file.
Previous measure: = VAR _prevdate = MAXX ( FILTER ( ALL ( 'public sales_reports'[sale_dt] ), 'public sales_reports'[sale_dt] < MAX ( 'public sales_reports'[sale_dt] ) ), 'public sales_reports'[sale_dt] ) RETURN IF ( HASONEVALUE ( 'public sales_reports'[sale_dt] ), CALCULATE ( [current measure:], 'public sales_reports'[sale_dt] = _prevdate ) )
2 Replies
Hi,
In case there is no calendar table, please try like the below and the attached pbix file.
Previous measure: = VAR _prevdate = MAXX ( FILTER ( ALL ( 'public sales_reports'[sale_dt] ), 'public sales_reports'[sale_dt] < MAX ( 'public sales_reports'[sale_dt] ) ), 'public sales_reports'[sale_dt] ) RETURN IF ( HASONEVALUE ( 'public sales_reports'[sale_dt] ), CALCULATE ( [current measure:], 'public sales_reports'[sale_dt] = _prevdate ) )Thank you very much for your help!
It worked perfectly.
