Forum Discussion
How to create 7-day bins
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 b...
rajulshah
Resident Rockstar
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 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
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
Many thanks