topic Re: grouping dates dynamically in DAX Commands and Tips

grouping dates dynamically

Anonymous — Tue, 30 Jul 2019 05:35:39 GMT

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

Re: grouping dates dynamically

Stachu — Tue, 30 Jul 2019 11:25:35 GMT

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

Re: grouping dates dynamically

Anonymous — Wed, 31 Jul 2019 06:09:52 GMT

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.

Re: grouping dates dynamically

Stachu — Fri, 02 Aug 2019 05:01:57 GMT

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?

Re: grouping dates dynamically

spandy34 — Fri, 24 Jun 2022 18:26:15 GMT

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))