Split & filter data from one table into multiple based on a user selected date

[AndyTrezise]

Hi All

I have a requirement to split / filter a table of transactions into three consecutive date driven periods.

My base table consists of tasks for resources on given dates.

i.e.

John, Project A, Task 1, 01/02/2017

John, Project G, Task 2, 03/04/2017

John, Project D, Task 3, 04/04/2017

John, Project G, Task 4, 30/04/2017

John, Project N, Task 5, 04/05/2017

John, Project A, Task 6, 14/05/2017

John, Project D, Task 7, 10/06/2017

So...my requirement is to allow the user to select a year / month from a date slicer (I'm OK with that bit) and then based on the month selected I then want to display 3 seperate tables of data. For example if the user select February 2017 then I want table 1 to display tasks in February, table 2 to display tasks in March and table 3 to display tasks in April etc.

I managed to split the table into 3 via the query editor based on todays date but I need it to be more flexible than that and allow a user to select the start month/year and work forward from there. I'm sure there must be a way of creating the tables on the fly or filtering via DAX.

Any help would be much appreciated.

[v-chuncz-msft · Accepted solution]

@AndyTrezise,

You may add three measures and drag them to three tables accordingly. It takes advantage of Show Categories With No Data.

Measure =
VAR d =
    SELECTEDVALUE ( Table1[Date] )
VAR d2 =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    IF (
        d >= DATE ( YEAR ( d2 ), MONTH ( d2 ), 1 )
            && d
                < DATE ( YEAR ( d2 ), MONTH ( d2 ) + 1, 1 ),
        d
    )

Measure2 =
VAR d =
    SELECTEDVALUE ( Table1[Date] )
VAR d2 =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    IF (
        d
            >= DATE ( YEAR ( d2 ), MONTH ( d2 ) + 1, 1 )
            && d
                < DATE ( YEAR ( d2 ), MONTH ( d2 ) + 2, 1 ),
        d
    )