Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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 Zhi
If 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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |