Forum Discussion
Help with a calculated column
Hi STS_Joshua ,
I am not sure whether this is what you want, you could try below calculated column to see whether it work or not
Column = var minday=MINX(ALLEXCEPT('Table (2)','Table (2)'[Week]), DAY('Table (2)'[Period Date])) return date('Table (2)'[Year],MONTH('Table (2)'[Period Date]), minday)Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It's fairly simple. I have a table setup similarly to the below:
| Period Date | Week | Year |
3/17/2018 | 11 | 2018 |
| 3/16/2019 | 11 | 2019 |
| 3/14/2020 | 11 | 2020 |
I'm looking to calculate a column that will return the latest date for the given week but in that week's year like below:
| Period Date | Week | Year | Adjusted Date |
3/17/2018 | 11 | 2018 | 3/14/2018 |
| 3/16/2019 | 11 | 2019 | 3/14/2019 |
| 3/14/2020 | 11 | 2020 | 3/14/2020 |
The idea being that Power BI is pretty bad at comparing one week to the same week in a previous year because the dates don't line up properly.
Hi STS_Joshua ,
I am not sure whether this is what you want, you could try below calculated column to see whether it work or not
Column = var minday=MINX(ALLEXCEPT('Table (2)','Table (2)'[Week]), DAY('Table (2)'[Period Date])) return date('Table (2)'[Year],MONTH('Table (2)'[Period Date]), minday)
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.