cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AndyTrezise Regular Visitor
Regular Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

@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
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

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

@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
    )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.