Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need DAX for Date Table

Hi All

 

 

 

I want to create the same Date table with the help of DAX in power bi like the below table. Observe the Weeks column, it is not a regular weeks column. I want DAX to create a Date Table, especially a weeks column like below. 

DatesYearMonthWeeksDays
31-10-20222022Octoberweek1Monday
01-11-20222022Novemberweek1Tuesday
02-11-20222022Novemberweek1Wednesday
03-11-20222022Novemberweek1Thursday
04-11-20222022Novemberweek1Friday
05-11-20222022Novemberweek1Saturday
06-11-20222022Novemberweek1Sunday
07-11-20222022NovemberWeek2Monday
08-11-20222022NovemberWeek2Tuesday
09-11-20222022NovemberWeek2Wednesday
10-11-20222022NovemberWeek2Thursday
11-11-20222022NovemberWeek2Friday
12-11-20222022NovemberWeek2Saturday
13-11-20222022NovemberWeek2Sunday
14-11-20222022NovemberWeek3Monday
15-11-20222022NovemberWeek3Tuesday
16-11-20222022NovemberWeek3Wednesday
17-11-20222022NovemberWeek3Thursday
18-11-20222022NovemberWeek3Friday
19-11-20222022NovemberWeek3Saturday
20-11-20222022NovemberWeek3Sunday
21-11-20222022NovemberWeek4Monday
22-11-20222022NovemberWeek4Tuesday
23-11-20222022NovemberWeek4Wednesday
24-11-20222022NovemberWeek4Thursday
25-11-20222022NovemberWeek4Friday
26-11-20222022NovemberWeek4Saturday
27-11-20222022NovemberWeek4Sunday
28-11-20222022NovemberWeek1Monday
29-11-20222022NovemberWeek1Tuesday
30-11-20222022NovemberWeek1Wednesday
01-12-20222022DecemberWeek1Thursday
02-12-20222022DecemberWeek1Friday
03-12-20222022DecemberWeek1Saturday
04-12-20222022DecemberWeek2Sunday
05-12-20222022DecemberWeek2Monday
06-12-20222022DecemberWeek2Tuesday
07-12-20222022DecemberWeek2Wednesday
08-12-20222022DecemberWeek2Thursday
09-12-20222022DecemberWeek2Friday
10-12-20222022DecemberWeek2Saturday
11-12-20222022DecemberWeek3Sunday
12-12-20222022DecemberWeek3Monday
13-12-20222022DecemberWeek3Tuesday
14-12-20222022DecemberWeek3Wednesday
15-12-20222022DecemberWeek3Thursday
16-12-20222022DecemberWeek3Friday
17-12-20222022DecemberWeek3Saturday
18-12-20222022DecemberWeek4Sunday
19-12-20222022DecemberWeek4Monday
20-12-20222022DecemberWeek4Tuesday
21-12-20222022DecemberWeek4Wednesday
22-12-20222022DecemberWeek4Thursday
23-12-20222022DecemberWeek4Friday
24-12-20222022DecemberWeek4Saturday
25-12-20222022DecemberWeek5Sunday
26-12-20222022DecemberWeek5Monday
27-12-20222022DecemberWeek5Tuesday
28-12-20222022DecemberWeek5Wednesday
29-12-20222022DecemberWeek5Thursday
30-12-20222022DecemberWeek5Friday
31-12-20222022DecemberWeek5Saturday
01-01-20232023JanuaryWeek1Sunday
02-01-20232023JanuaryWeek1Monday
03-01-20232023JanuaryWeek1Tuesday
04-01-20232023JanuaryWeek1Wednesday
05-01-20232023JanuaryWeek1Thursday
06-01-20232023JanuaryWeek1Friday
07-01-20232023JanuaryWeek1Saturday
08-01-20232023JanuaryWeek2Sunday
09-01-20232023JanuaryWeek2Monday
10-01-20232023JanuaryWeek2Tuesday
11-01-20232023JanuaryWeek2Wednesday
12-01-20232023JanuaryWeek2Thursday
13-01-20232023JanuaryWeek2Friday
14-01-20232023JanuaryWeek2Saturday
15-01-20232023JanuaryWeek3Sunday
16-01-20232023JanuaryWeek3Monday
17-01-20232023JanuaryWeek3Tuesday
18-01-20232023JanuaryWeek3Wednesday
19-01-20232023JanuaryWeek3Thursday
20-01-20232023JanuaryWeek3Friday
21-01-20232023JanuaryWeek3Saturday
22-01-20232023Januaryweek4Sunday
23-01-20232023Januaryweek4Monday
24-01-20232023Januaryweek4Tuesday
25-01-20232023Januaryweek4Wednesday
26-01-20232023Januaryweek4Thursday
27-01-20232023Januaryweek4Friday
28-01-20232023Januaryweek4Saturday
29-01-20232023JanuaryWeek1Sunday
30-01-20232023Januaryweek1Monday
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Anonymous 

     

    try to create the Date Table with this:

    DateTable =
    ADDCOLUMNS (
        CALENDAR (DATE(2022,10,31), DATE(2023,1,31)),
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "MMMM" ),
        "Weeks",
        "Week"&
            VAR _WN = WEEKNUM([Date],1)
            RETURN
            IF(
                _WN>52,
                5,
                IF(
                MOD( _WN,4)=0,
                4,
                    MOD( _WN,4)
                )
            ),
        "Days",FORMAT ( [Date], "DDDD" )
    )
     
    i tried and it worked like this:

     

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Anonymous 

     

    try to create the Date Table with this:

    DateTable =
    ADDCOLUMNS (
        CALENDAR (DATE(2022,10,31), DATE(2023,1,31)),
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "MMMM" ),
        "Weeks",
        "Week"&
            VAR _WN = WEEKNUM([Date],1)
            RETURN
            IF(
                _WN>52,
                5,
                IF(
                MOD( _WN,4)=0,
                4,
                    MOD( _WN,4)
                )
            ),
        "Days",FORMAT ( [Date], "DDDD" )
    )
     
    i tried and it worked like this:

     

     

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    How are the weeks calculated? 

    31-10-2022 2022 October week1 Monday
    27-01-2023 2023 January week4 Friday
    28-01-2023 2023 January week4 Saturday
    29-01-2023 2023 January Week1 Sunday
    30-01-2023 2023 January week1 Monday
    01-01-2023 2023 January Week1 Sunday

     

    Based on the sample data, you have two Sundays for week 1  in January.