Power Bi

[Sunila]

Hi,

I am trying to create a 13 week bucket from todays date and would like to show my dates in 13 columns as shown in screen shot?

I created WeekStartdate =today() and First week = Toay()+7 and concatenate both columns to get my First week column but it requires creating quite many columns. Could anyone suggest a DAX for the same?

[v-cgao-msft · Accepted solution]

Hi @Sunila ,

Please consider creating a new table like this in the PowerQuery Editor:

This allows you to place the [Week] column on the x-axis and create filters in the measure with [WeekStartDate] and [WeekEndDate].

Of course you can also obtain such a table by following some steps:

Group + Visual filter may also help.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[v-cgao-msft · Accepted solution]

Hi @Sunila ,

Please consider using the first method, which will be calculated dynamically during the refresh. Create a new blank query and paste this code into the advanced editor.

let
    Source = {Int32.From(DateTime.LocalNow())..Int32.From(DateTime.LocalNow())+6*14+1},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddDays([Column1],6)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each Date.DayOfWeek([Column1])=Date.DayOfWeek(DateTime.LocalNow())),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each Date.ToText([Column1],[Format="dd/M"]) & "-" & Date.ToText([Custom],[Format="dd/M"])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Column1", "WeekStartDate"}, {"Custom", "WeekEndDate"}, {"Custom.1", "Week"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"WeekStartDate", type date}, {"WeekEndDate", type date}})
in
    #"Changed Type1"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Sunila]

Hi @v-cgao-msft , 

Thank you for your input. It is quite helpful.

In the attached demo file,though, week start date is not dynamically updated to the current date. When I downloaded the file a couple of hours ago, the week started from the current date(24/05), but, now it shows week start date from a week after(31/05).

[v-cgao-msft · Accepted solution]

Hi @Sunila ,

Please consider using the first method, which will be calculated dynamically during the refresh. Create a new blank query and paste this code into the advanced editor.

let
    Source = {Int32.From(DateTime.LocalNow())..Int32.From(DateTime.LocalNow())+6*14+1},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddDays([Column1],6)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each Date.DayOfWeek([Column1])=Date.DayOfWeek(DateTime.LocalNow())),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each Date.ToText([Column1],[Format="dd/M"]) & "-" & Date.ToText([Custom],[Format="dd/M"])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Column1", "WeekStartDate"}, {"Custom", "WeekEndDate"}, {"Custom.1", "Week"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"WeekStartDate", type date}, {"WeekEndDate", type date}})
in
    #"Changed Type1"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Sunila · Accepted solution]

Hi Gao,

I did use the first method only and paste the code in Advanced editor.  Filter step(pls refer screen shot), filters out the most recent week. I need a 13 week bucket from current date(25/05-31/05,  01/06-06/06 and so on. Thank you for your answers, it really helped until now. Could you also please assist to get the week starting from current date?

[Sunila]

@v-cgao-msft 

It did work. Thank you so much.

[v-cgao-msft · Accepted solution]

Hi @Sunila ,

Please consider creating a new table like this in the PowerQuery Editor:

This allows you to place the [Week] column on the x-axis and create filters in the measure with [WeekStartDate] and [WeekEndDate].

Of course you can also obtain such a table by following some steps:

Group + Visual filter may also help.

Use grouping and binning in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum