CODEWITHSUNDEEP
data-visualizationpowerbidax
Serdia
Serdia

Reputation: 4428

Get "Week of 2nd" from Date field in power BI

I have a date field in my DimDate table.

enter image description here

I want to get another column WeekOf that would show the week number based on Monday. For example I have date:

  Date         WeekOf
10/2/2017   Week of 2nd
10/9/2017   Week of 9th
10/16/2017  Week of 16th

Upvotes: 0

Views: 285

Answers (1)

Joe G
Joe G

Reputation: 1776

Creating a new column with the following formula should give you what you want.

If you every want to change it to be the Week of a different day, change the 2 in the TargetDate variable to which ever day of the week you want.

WeekOf = 
    VAR TargetDate = DAY(DATEADD(Dates[Date], 2 - WEEKDAY(Dates[Date]), DAY))
    VAR TargetDateText = CONCATENATE(TargetDate, SWITCH(TargetDate, 1, "st", 21, "st", 31, "st", 2, "nd", 22, "nd", 3, "rd", 23, "rd", "th"))  

    RETURN
        CONCATENATE("Week of ", TargetDateText)

Results

Upvotes: 2

Related Questions