Need help with custom week based on user selection of date range

[mukul_jain]

Hi All,

I think this might be a simple problem but I have been banging my head to solve this.

Basically I have a table with sales data (Sales Qty, Value and sale date) as follows:

Now with excel, when we apply pivot, we can defined group based on custom start date and the number of days to group:

I want to replicate the same in Power BI, to give user the ability to define a date range and make 7 days groups based on the starting date from user input.

Since both are Microsoft product, I assume this should already be possible in Power Bi but am not able to figure out the solution. Please help. Thanks in advance!!

(Kudos to the community, a good platform to learn from other users)

[V-pazhen-msft]

@mukul_jain 

You could create a "new group" for the date column, I believe this is similar to the grouping in excel. 

Paul Zheng

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

[mukul_jain]

Hi @V-pazhen-msft ,

The week dates are still not dynamically changing with this option (no way to take user input). It is taking the min and max as the start and end of range by default.

Thanks 

[Tahreem24]

@mukul_jain ,

For your reference please see the below screen shot.

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

[mukul_jain]

Relative date filtering only gives a particular range data, for example last 7 days from the current date. However, my requirement is different. For example, if the user selects the period 10-Jan-2019 to 21-Feb-2019 in the date slicer, I want the data to be grouped by 7 days starting the user selected date range and show up as follow:

                                                | Sale Qty   |  Sale Value  |

10-Jan-2019 -- 16-Jan-2019      xxx              xxxx

17-Jan-2019 -- 23-Jan-2019      xxx              xxxx

24-Jan-2019 -- 30-Jan-2019      xxx              xxxx

 and so on..

I hope this clarifies my initial requirement

[Tahreem24]

@mukul_jain ,

Create 3 calculated column to get the week range.

1) Start_Week =

                   FORMAT(

                   (TABLE[DATE]-WEEKDAY(TABLE[DATE],1)

                   +5

                    ),

                   "DD-MMM-YY"

                   )

2) End_Week = FORMAT(

                   (TABLE[DATE]-WEEKDAY(TABLE[DATE],1)

                      +12

                   ),

                  "DD-MMM-YY"

                    )

3) WEEK RANGE = TABLE[Start_Week]&" To "&TABLE[End_Week]

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

[mukul_jain]

@Tahreem24 :Thanks for the support but still doesn't meet my requirement. There is no option to take input from user for start date. As per the code, you are hard coding the offset by 5 days.

[Tahreem24]

@mukul_jain ,

Basically, you'll need to use the relative date filter.

Take the Slicer from Visualization Pane then put your Date field here and then click on drop down arrow on slicer and select Relative.

Don't forget to hit THUMBS UP and mark it as a solution if it helps you!