How to create 7-day bins

[marypal]

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.22
2nd bin: 04.10.22 - 10.10.22 
3rd bin: 11.10.22 - 17.10.22
4th bin: 18.10.22 - 24.10.22
5th bin: 25.10.22 - 31.10.22

Could you please suggest how this can be implemented.
I tried Power BI bins but it's impossible to implement the above logic.

Many thanks

 

[rajulshah]

@marypal 

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

Please let me know if this didn't work.

[marypal]

Hello @rajulshah ,

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]

@marypal 

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]

Hi @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 measures

EndDate = MAX('report_dates'[short_date])

MonthStartDate = EOMONTH([EndDate],-1)+1

I 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)+1

VAR delta = MOD(numDays,7)

RETURN IF(delta=0,[MonthStartDate],[MonthStartDate]-(7-delta))

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(

    COUNTA('fact_table'[client_id]),

    'fact_table'[type]

        IN { "target" },

        KEEPFILTERS(DATESBETWEEN('dim_dates'[short_date],[StartBinDate],[EndDate])))

At the end we should have the following visualisation
when on X-axis we have the start date of the bin

But the preferable option to display the end date of a bin

Could you please suggest how to create and display bins for the period [StartBinDate] - [EndDate]?

Many thanks

[rajulshah]

@marypal 

I am sorry I am still confused as what you need as output.