Wednesday, 1 June 2022

Extract First or Last Date of the Week | Date functions in DAX | Power BI

 Extract the First Day of this Week

1. Go to DATA, Add new columns, 

Get Current Date Time

= TODAY()                        // current datetime

Get Current Day, Month, MonthName, Quarter, Year etc

= TODAY() // current datetime

= mytable[datecolumn].[Quarter]

= mytable[datecolumn].[Day]

= mytable[datecolumn].[Month]

= mytable[datecolumn].[Year]


Format a date using formula
todayname = format(today(),"dddd")            //format to extract the Name of the date.

Get a Week Number of a Date

weekDay= WEEKDAY(TODAY()) //return the day number, it can take 1,2,3 as parameter to start the count from saturday, sunday or monday.

Get the First Day of the Week
firstDayofThisWeek = today() - WEEKDAY(TODAY()) +1 //today - number of day +1

Get the Last Day of the Week

lastDayofThisWeek = today() - WEEKDAY(TODAY()) +7 //today - number of day +7


Get the First Day of the Month
firstDayofThisMonth = STARTOFMONTH(TODAY()) //START OF MONTH

Get the Last Day of the Month

lastDayofThisMonth = ENDOFMONTH(TODAY()) //END OF MONTH


Populate a table/column with dates in a range

ourdates = calendar(date(2022,01,01) , date(2022-01-31) ) //first and last day as parameter


1 comment: