Forum Discussion
How to get daily dates and week date start
I have a date column with dates starting from 1st of every month for example: 01/01/2023, 01/02/2023...... till 01/12/2023. I want to convert this date column into daily dates column and then get week date start column. basically i want to get the week start date for every week between 01 jan 2023 to 31 dec 2023. I tried creating another table with individual dates column and then getting week start date but when i relate the two tables, I only get one week start date for every month. Is there a way i can convert this 01/01/2023, 01/02/2023...... till 01/12/2023 into daily dates and then get the week start date.
thanks
4 Replies
- foodd
Community Champion
Using DAX, create a calculated column on your data table and use the following pattern formula, and the
start of the week is Monday:
Week Start Date = Data[Date] – WEEKDAY(Data[Date],2) + 1
https://dax.guide/functions/date-time/
This formula only gives the week start date for the dates 01/01/2023, 01/02/2023...... I want to get week start dates for every week starting from Monday between 01 jan 2023 and 31 dec 2023.
- foodd
To your data table, add the formula as a calculated column. Here is a screenshot of what it would look like with a shorted dataset; however, applies to the whole year of 2023.
Please add your work-in-progress Power BI Desktop file, and I will take a look.
Thanks for the solution. I have this column and in photo 1 is what i am trying to get that is all week's start date from 1 Jan 2023 to 31 dec 2023(ignore year 2024) but because i only have dates in photo 2 for 1st day of month, i get week start date for this first week. i want to get this photo 2 dates first drill down to daily dates and than drill down to week start date.
i cannot share the original files due to security reasons.
