How to display week from date

[beacon77]

Hi All,

Is there a function that gives the week range from SUNDAY to SATURDAY inclusive of both days.

Thanks

[v-yueyunzh-msft · Accepted solution]

Hi  , @beacon77 

You can create a  date table use this M language in "Advanced Editor" in Power Query Editor:

= Table.AddColumn(Table.Combine(List.Transform( List.Zip({List.Generate(()=>#date(2022,1,2),(x)=> x<=#date(2022,12,31),(x)=>Date.AddDays(x,7)) ,List.Generate(()=>#date(2022,1,8),(x)=> x<=#date(2022,12,31),(x)=>Date.AddDays(x,7))}) ,(x)=>Table.FromRecords({Record.FromList(x,{"Start_date","End_date"})},{"Start_date","End_date"})      ))  ,"weekrange",  (x)=> Combiner.CombineTextByDelimiter("-")( {Text.From(x[Start_date]),Text.From(x[End_date])})    )

Then you can get the date table you want to need , like this:

Then you can use this table to calcualte the count you need use the dax function, such as FILTER() and COUNTROWS() i think.

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[beacon77]

@v-yueyunzh-msft  thanks I cannot create the table, i am getting syntax error

The syntax for 'Table' is incorrect. (DAX(Table.AddColumn(Table.Combine(List.Transform( List.Zip({List.Generate(()=>#date(2022,1,2),(x)=> x<=#date(2022,12,31),(x)=>Date.AddDays(x,7)) ,List.Generate(()=>#date(2022,1,8),(x)=> x<=#date(2022,12,31),(x)=>Date.AddDays(x,7))}) ,(x)=>Table.FromRecords({Record.FromList(x,{"Start_date","End_date"})},{"Start_date","End_date"}) )) ,"weekrange", (x)=> Combiner.CombineTextByDelimiter("-")( {Text.From(x[Start_date]),Text.From(x[End_date])}) ))).

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

Provide example input data as table text (not a screen print or SQL code). 

Simple copy and paste the tabnle into this chat so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.

Remember we are unpaid voluinteers, who are keen to help but wont faff about doing data prep. 

You want my help then please put in effort to provide crystal clear decsriptions and examples.

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.

Remember not to share private data ... we don't want you to get into trouble. 😧

Take care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

[beacon77]

@speedramps thanks and my aapologies, please see the code

  create table ship (id int not null,ShipDtTm daate time)
  insert into ship values (1520655,	10/9/22 12:40 PM)
(1520656,	10/9/22 12:40 PM)
(1520658	,10/15/22 12:40 PM)
(1520659	,10/15/22 12:40 PM)

, if you notice this dates are for last week October 9th SUNDAY and October 15th SATURDAY , So i want to display the count of the id's by week and want to show the week range next to the count, like below.

Thanks

count	weekrange
4	10/09/2022 - 10/15/2022