DAX statement for the Calendar table

[Chapin4u]

Hi, need help to add this column in my Calendar table where I have date and week_number as columns. So my new column, I need to put the week number and the first and last day of the week for example:

                                                                 29 07/16 to 07/22

Thanks in advance for any help.

[dilumd · Accepted solution]

Hi

I'm not sure whether I understood your problem correctly, however M query to reach to the answer is as follows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzpVgdINcIlWuMyjVB5Zqics0Q3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Date]), type number),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Year", "Start of Week", each Date.StartOfWeek([Date]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Week", "Month", each Date.Month([Date]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Date]), type number),
    #"Inserted Day1" = Table.AddColumn(#"Inserted Day", "Day.1", each Date.Day([Start of Week]), type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Day1",{{"Day.1", "Week Start Day"}}),
    #"Inserted Day2" = Table.AddColumn(#"Renamed Columns", "Day.1", each Date.Day([End of Week]), type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Day2",{{"Day.1", "Week End Day"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Week of Year", type text}, {"Week Start Day", type text}, {"Week End Day", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "WeekNo_Start&End Date", each [Week of Year]&" "&[Week Start Day]&"/"&[Week End Day]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"WeekNo_Start&End Date", type text}})
in
    #"Changed Type2"

[dilumd · Accepted solution]

Hi

I'm not sure whether I understood your problem correctly, however M query to reach to the answer is as follows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NzpVgdINcIlWuMyjVB5Zqics0Q3FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Week of Year" = Table.AddColumn(#"Changed Type", "Week of Year", each Date.WeekOfYear([Date]), type number),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Year", "Start of Week", each Date.StartOfWeek([Date]), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Week", "Month", each Date.Month([Date]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Date]), type number),
    #"Inserted Day1" = Table.AddColumn(#"Inserted Day", "Day.1", each Date.Day([Start of Week]), type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Day1",{{"Day.1", "Week Start Day"}}),
    #"Inserted Day2" = Table.AddColumn(#"Renamed Columns", "Day.1", each Date.Day([End of Week]), type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Day2",{{"Day.1", "Week End Day"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Week of Year", type text}, {"Week Start Day", type text}, {"Week End Day", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "WeekNo_Start&End Date", each [Week of Year]&" "&[Week Start Day]&"/"&[Week End Day]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"WeekNo_Start&End Date", type text}})
in
    #"Changed Type2"

[prakash_gautam · Accepted solution]

Hello,

A DAX expression could:

WEEK = 
[WEEKNO] & " "
    & CALCULATE (
        MIN ( [Date] ),
        FILTER (
            ALL ( 'KALENDER' ),
            [WEEKNO] = EARLIER ( [WEEKNO] )
                && [YEARNO] = EARLIER ( [YEARNO] )
        )
    )
    & " to "
    & CALCULATE (
        MAX ( [Date] ),
        FILTER (
            ALL ( 'KALENDER' ),
            [WEEKNO] = EARLIER ( [WEEKNO] )
                && [YEARNO] = EARLIER ( [YEARNO] )
        )
    )

Thanks