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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
techy
Regular Visitor

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 4
techy
Regular Visitor

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.

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.

 

foodd_0-1690074970365.png

 

Please add your work-in-progress Power BI Desktop file, and I will take a look. 

techy
Regular Visitor

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.Screenshot 2023-07-24 084555.png

Screenshot 2023-07-24 083936.png

foodd
Super User
Super User

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/

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors