Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

grouping dates dynamically

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 kno...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Stachu
Community Champion
7 years ago

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's avatar
    spandy34
    Responsive Resident
    4 years ago

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