Forum Discussion
How to create 7-day bins
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.
Resident RockstarSo 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?
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 measuresEndDate = 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)+1VAR 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 binBut the preferable option to display the end date of a binCould you please suggest how to create and display bins for the period [StartBinDate] - [EndDate]?
Many thanks