Forum Discussion
Help with a calculated column
Hi All,
I want to calculate a column. Every week I receieve sales data on saturday but I would like to normalize these dates.
I want to calculate a column that will take the weeknumber of all inputs then change the date to whatever the current date would be for that week.
For example: week 11 of 2020 ended on 3/14/2020 but ended on 3/16/2019 and 3/17/2018. I want a column that will change the date for an ending period to match the latest ending period for that date, basicailly to make whatever the input date value for 2019/2018/2017 for week# to match the date for the latest year.
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.
5 Replies
I think what you're describing is a "Week Ending" column. Try searching the forums for Date Table help. That should achieve what you're looking for. Hope this helps
Hi STS_Joshua ,
I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
