How do I take a list of dates and creates dates groups (example in post)?

[Anonymous]

Hi,

I have the below table:

Month                    Date

January 2017          06/01/2017

February 2017        17/02/2017

March 2017            09/03/2017

April 2017               22/04/2017

I'm looking to slice a transactions table by the date ranges above. For example, I need to create date ranges like:

Month                    Date                 EndOfDateRange

January 2017          06/01/2017     16/02/2017

February 2017        17/02/2018     08/03/2017

March 2017            09/03/2017     21/04/2017

April 2017               22/04/2017     xx/xx/xxxx

I will then use the 'Month' as a filter in the visual to retrieve all rows from the transaction table where the transaction date is in the date range for the filtered 'Month'.

For example,

Filter on 'Month' of March 2017 (using the date rate of 09/03/2017 - 21/04/2017) it looks up all rows in the transaction table and returns:
Alan   157   13/03/2017

Jim     221   21/03/2017

Steve  115   21/04/2017

How do I do this?   I will be able to work the table relationships but how do I create the date ranges starting with the original dataset?

I'm trying pivoting but I'm getting in a pickle.

Thanks in advance.

[Anonymous · Accepted solution]

Hi @Anonymous,

You can refer to below steps to achieve your requirement.

1. Add a calculate column to store EndOfDateRange.

EndOfDateRange = MINX(FILTER(ALL(Table1),[Date]>EARLIER([Date])),[Date])-1

2. Add a measure to fact table to check if current date are in the filter date range.

Check = 
var selectitem=IF(HASONEFILTER(Table1[Month]),VALUES(Table1[Month]),BLANK())
return
IF(MAX(Sheet2[Date])>=LOOKUPVALUE(Table1[Date],Table1[Month],selectitem)&&MAX(Sheet2[Date])<=LOOKUPVALUE(Table1[EndOfDateRange],Table1[Month],selectitem),1) 

3. Create table visual and filter on measure to show non blank records.

4. Add slicer to filter records.

Regards,

Xiaoxin Sheng