Working with dates in Power BI

Hello friends! Today we’ll be learning to working with dates in Power BI.

We’ll create columns for the date fields

Year

Year = YEAR(Sales[Date])         //2020
Year = FORMAT(Sales[Date],"Y")  // Day of the year 1 to 365 or 366, 13/04/2020 is 104
Year = FORMAT(Sales[Date],"YY")  // 20
Year = FORMAT(Sales[Date],"YYY")  // year and day of the year, 20104 for 13/04/2020
Year = FORMAT(Sales[Date],"YYYY")  // 2020

Month

Month = MONTH(Sales[Date])              // 04
Month = FORMAT(Sales[Date],"M")         // 4
Month = FORMAT(Sales[Date],"MM")        // 04
Month = FORMAT(Sales[Date],"MMM")       // Apr
Month = FORMAT(Sales[Date],"MMMM")     //  April

Day

Day = Day(Sales[Date])              // 1 to 31
Day = FORMAT(Sales[Date],"D")       // Day of the month, 1 to 31, 09/04/2020 is 9
Day = FORMAT(Sales[Date],"DD")       // Day of the month, 1 to 31, 09/04/2020 is 09
Day = FORMAT(Sales[Date],"DDD")      // Mon
Day = FORMAT(Sales[Date],"DDDD")     // Monday

Weekday

WEEKDAY = WEEKDAY(Sales[Date],N)        
// where N is 1(Sunday=1 to Saturday=7),2(Monday=1 to Sunday=7),3(Monday=0 to Sunday=6)
WEEKDAY = FORMAT(Sales[Date],"W")       // weekday by default start from Sunday
WEEKDAY = FORMAT(Sales[Date],"WW")      // week number by default start from Sunday
WEEKDAY = FORMAT(Sales[Date],"WWW")         
// Week number and day of the week, 13/04/2020, 162, 16th week and 2nd day of week, 
by default start from Sunday

Week end date

WeekEnd Date = Sales[Date] - WEEKDAY(Sales[Date],2) + 7    // Week starts from Monday

Week start Date

WeekStart Date = Sales[Date] - WEEKDAY(Sales[Date],2) + 1  // Week starts from Monday

Week Number

WeekNumber = WEEKNUM(Sales[Date],N)   
// where N is 1(Week starts from Sunday),2(week starts from Monday)

Custom Fiscal Month

Get fiscal month for custom fiscal year

FiscalMonth =   IF (( MONTH(Sales[Date]) - (MONTH(DATE(2020,4,1))-1))>0, 
MONTH(Sales[Date]) - (MONTH(DATE(2020,4,1))-1), 
MONTH(Sales[Date]) - (MONTH(DATE(2020,4,1))-1) + 12)
// If Fiscal year start from 1st April 
// If Fiscal year start from 1st May change the month to May 

Keep visiting Analytics Tuts for more tutorials.

Thanks for reading! Comment your suggestions and queries