How to create 7-day bins

Question

Hi,I need help with the following task.We have for example a table with orders and date. I need to calculate how many orders were created every week and display that on the chart. The 7-day bins should be created in the following way: the last date of a chosen on a slicer month is our final date, then we go back every 7 days, the last bin will include the days from the previous month such that to complete the whole period of 7 days.For example, we chose October 2022.1st bin: 27.09.22 - 03.10.222nd bin: 04.10.22 - 10.10.22 3rd bin: 11.10.22 - 17.10.224th bin: 18.10.22 - 24.10.225th bin: 25.10.22 - 31.10.22Could you please suggest how this can be implemented.I tried Power BI bins but it's impossible to implement the above logic.&nbsp;Many thanks&nbsp; &nbsp;

rajulshah · Answer

marypal&nbsp;&nbsp;You can create bins using the following logic:This would create bins in the date table. Then you can create another column to get the last date of the week with the following DAX:WeekLastDate = Dates[Date (bins)] + 6&nbsp;Please let me know if this didn't work.

marypal · Answer

Hello&nbsp;rajulshah&nbsp;,Thank you for your reply.Unfortunately, this did not work. In the report I have a slicer to choose a month. So it is not possible to choose the last date of the period to create bins, they are dynamic.

rajulshah · Answer

marypal&nbsp;So that would work, right?Can you give me an example of what the date slicer selection is and how this solution doesn't work for you?

marypal · Answer

Hi&nbsp;rajulshah,Yes, I suppose I need to provide more details on my task.On the slicer I choose a month (for example March 2023). My slicer is based on a table report_dates, it is not linked to the fact_table, so I calculate the measuresEndDate = MAX('report_dates'[short_date])MonthStartDate = EOMONTH([EndDate],-1)+1I calculated a measure, that finds a start date for the displayed period (the date that will be in the previous month such that to complete 7-day period)StartBinDate = VAR numDays = DATEDIFF([MonthStartDate],[EndDate],DAY)+1VAR delta = MOD(numDays,7)RETURN IF(delta=0,[MonthStartDate],[MonthStartDate]-(7-delta))&nbsp;It returns me the correct start date of the first bin.Then I created the measure that counts number of target clients (this amount should be displayed for each bin)Count Clients = CALCULATE(&nbsp; &nbsp; COUNTA('fact_table'[client_id]),&nbsp; &nbsp; 'fact_table'[type]&nbsp; &nbsp; &nbsp; &nbsp; IN { "target" },&nbsp; &nbsp; &nbsp; &nbsp; KEEPFILTERS(DATESBETWEEN('dim_dates'[short_date],[StartBinDate],[EndDate])))At the end we should have the following visualisationwhen on X-axis we have the start date of the binBut the preferable option to display the end date of a bin&nbsp;Could you please suggest how to create and display bins for the period [StartBinDate] - [EndDate]?Many thanks

rajulshah · Answer

marypal&nbsp;&nbsp;I am sorry I am still confused as what you need as output.

Forum Discussion

How to create 7-day bins

5 Replies

Related content

Age Bins

Workspace recycle bin

How to create 7-day bins

create a bin column of a measure

Changing Incremental Refresh Window from 14 Days to 7 Days