grouping dates dynamically

[Anonymous]

Hi All, 

I want to be able to group dates into a list where each value contains a range of 14 days (fortnight period), however, i need it to be done dynamically. Any idea how to do that? 

I know that you can manually go and create groups, but i want it to dynamically create groups of 14 days. 

Thanks all 

[Stachu]

I don't understand what does dynamically mean in this context - when would it change?

[Anonymous]

Ideally, when i select a date on a slicer, i want it to show me 3 months before that date but i was wondering if it's possible to, when selected, the view of the previous 3 months is shown in a bar or line chart and the dates that occur in those three months has grouped into fortnight groups.  

[Stachu]

hmm, bins are essentially an additional UI to create a calculated column. Calculated columns do not change based on a slicer changes, only when the table is refreshed.

Now you can write DAX that would group the column differently at every refresh (e.g. last 14 days with current date always at the start), see below

Column = "Fortnight " & INT(DIVIDE('Calendar'[Date] - TODAY(),14))

but you only a measure can change based on a slicer selection

so ideally you would like to select e.g. Feb 2019, and a barchart would display data for Nov 2018-Jan 2019?
what would you do with the fortnight bins in this scenario?

[spandy34]

Hi 

Can I do this but calculate it every 28days?  I have tried below as I want 28 day periods and the column to indicate the Period Number.  I had a go but because new to DAX its not working.  

Period No = "Period Number " & INT(DIVIDE('Procurement Data'[DateOfVisit] - 12,11,2021,28)

What I want is a column that identifies the Period Number based on the DateofVist but instead of having to type the dates in each year I just want the system to calculate the periods for the year and then after Period 13 go back to period 1 based on a 28 day cycle.  Here is the table below

Column = "Fortnight " & INT(DIVIDE('Calendar'[Date] - TODAY(),14))