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 unders...
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

     

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

rezaaditia's avatar
rezaaditia
Helper III
7 years ago

Thanks a lot Anonymous , its working :)

but just to make sure if what i am doing is right

 

so i added new column "YearWeek" and then put it in axis under weekname.

This is correct or you have another method ?

Thanks