Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
STS_Joshua
Helper II
Helper II

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.

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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.

 

View solution in original post

5 REPLIES 5
dax
Community Support
Community Support

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 DateWeekYear

3/17/2018

112018
3/16/2019112019
3/14/2020112020

 

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 DateWeekYear

Adjusted Date

3/17/2018

1120183/14/2018
3/16/20191120193/14/2019
3/14/20201120203/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.

dax
Community Support
Community Support

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.

 

CoreyP
Solution Sage
Solution Sage

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

Greg_Deckler
Super User
Super User

Like Week Ending? https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293#M120

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.