Forum Discussion

rezaaditia's avatar
rezaaditia
Helper III
7 years ago
Solved

create weekly date

Hi -

 

please help with the dax formula to create combination of week and date of every monday.

basically we want to create weekly report and if we use week numbers, it will be difficult to understand in which range date of the charts, hence we want to put the date as of monday date.

 

I use [weekday]-2, so everything under 1-7 should follow date no.1 (monday's date). 

 

the achievement will be W-MM/DD, sample as below

 

 

Thanks

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi rezaaditia ,

     

    I hope you have a calendar ( date )  table in your model.

     

    Do the following

     

    1. Create a calculated column called Week 

                          Week = WEEKNUM([Date],2)
     
    2. Create another calculated column WeekName
        WeekName = "W-" &
                   FORMAT( 
                   CALCULATE( MIN('Calendar'[Date]),
                    Filter('Calendar','Calendar'[Week] = EARLIER('Calendar'[Week])                 )
                      )
                     ,"MM/DD")
     This will give the output as desired.
     
    Cheers
     
    CheenuSing
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi rezaaditia ,

     

    Try the following

     

    1. Add another column

         YearWeek = Year(Table[Date]) * 100 + Table[WeekNum]

     ( Replace Table by your tablename)

     

    2. Using the modelling tab set the Sort order for WeekName to YearWeek.

     

    Cheers

     

    CheenuSing

  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi rezaaditia ,

     

    You should not add the YearWeek in the axis name.

     

    what you should do is select the Calculated column WeekName in the Fields pane.

     

    Go to the modelling pane

     

    From the sort by column set it to YearWeek.

     

    Cheers

     

    CheenuSing

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi rezaaditia ,

     

    I hope you have a calendar ( date )  table in your model.

     

    Do the following

     

    1. Create a calculated column called Week 

                          Week = WEEKNUM([Date],2)
     
    2. Create another calculated column WeekName
        WeekName = "W-" &
                   FORMAT( 
                   CALCULATE( MIN('Calendar'[Date]),
                    Filter('Calendar','Calendar'[Week] = EARLIER('Calendar'[Week])                 )
                      )
                     ,"MM/DD")
     This will give the output as desired.
     
    Cheers
     
    CheenuSing
    • rezaaditia's avatar
      rezaaditia
      Helper III
      7 years ago

      Hi Anonymous 

       

      its working, great formula and simple :)

      another question, how to make the weekname sort like calendar yah, its should be started 

      W-06/10, W-06/17, W-07/01 and .....

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi rezaaditia ,

         

        Try the following

         

        1. Add another column

             YearWeek = Year(Table[Date]) * 100 + Table[WeekNum]

         ( Replace Table by your tablename)

         

        2. Using the modelling tab set the Sort order for WeekName to YearWeek.

         

        Cheers

         

        CheenuSing

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi rezaaditia 

     

    Please see the below Query Editor solution.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdGxDYNAEEXBXogtcbtnzKcWRP9tQOJgCF820jvPZdQ69rVHHcv1ebLNaX7NzfyZuxnzIGuYqkpVqSpVpapUlapSVapaVatqVa2qVbWqVtWqWlWrmqrm6+CTcWgcGofGoXFoHBqHxqFxaBwah8ahceg/rxs=", 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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "week", each Date.WeekOfYear([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"week"}, {{"weekStart", each List.Min([Date]), type date}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Week Day", each "W-" & Text.PadStart( Number.ToText( Date.Month( [weekStart] ) ), 2, "0" ) & "/" & Text.PadStart( Number.ToText( Date.Day( [weekStart] ) ), 2, "0" ), type text),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"week"}, #"Added Custom1", {"week"}, "Added Custom1", JoinKind.LeftOuter),
    #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom1", {"Week Day"}, {"Week Day"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Added Custom1",{"Date", "Week Day"})
in
    #"Removed Other Columns"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    hi rezaaditia ,

     

    Use this formula,

     

    W-MM/DD = "W-"&MONTH('Table (2)'[Date])&"/"&'Table (2)'[Day]
     
     
    see above screenshot,
     
    I created startofweek new column ==> Edit queris ->click on Your date column -> click on add column (top of header)->Date(i'll showing some options) -> Go week-> startof week
     
    Day & Month  same this two columns Follow above process
     
    regards,
    naveen