cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dpombal Member
Member

Use a slicer to filter rows with Date From and Date To

 

 

Hi all I have a simply data model with a Calendar Table and a Fact Table with Date From and Date To fields.

 

Fact
1_fact table.PNG

 

 

 

 

Fact

Customer

DateStart

DateEnd

Operations

A

01/01/2018

07/01/2018

5

B

05/01/2018

15/01/2018

2

C

10/01/2018

20/01/2018

3

 

Since there is more than date 1 field (and joining tables is not trivial) on my fact table,

3_current_model.PNG

 

3_current_model.PNG

 

I have following requirement.

 

I require having a slicer to filter a measure to which displays my measure Sum Operations = Sum('Fact'[Operations]) having following requirement

Date Start (Fact) <= Date Selected (Calendar)<= Date End (Fact)

 

For example a table my new measure should be taken into account date fields

Date (Calendar)

new_measure

01/01/2018

5

02/01/2018

5

03/01/2018

5

04/01/2018

5

05/01/2018

5+2 =7

06/01/2018

5+2 =7

07/01/2018

5+2 =7

08/01/2018

2

09/01/2018

2

10/01/2018

2+3=5

11/01/2018

2+3=5

12/01/2018

2+3=5

13/01/2018

2+3=5

14/01/2018

2+3=5

15/01/2018

2+3=5

16/01/2018

3

17/01/2018

3

18/01/2018

3

19/01/2018

3

20/01/2018

3

21/01/2018

0

 

 

Taken into account I am using Direct Query, how can I manage to work with this double date data.

 

 

Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Use a slicer to filter rows with Date From and Date To

Hi @dpombal,

Based on my test, you could add two calculated columns in the calender table:

Column = RELATED('Fact'[Customer])
New = 
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( 'Table'[Date], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && NOT ( ISBLANK ( 'Table'[Column]) )
        )
    )
RETURN
    CALCULATE (
        MAX( 'Table'[Column] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = LastNonBlankDate )
    )

 

Result:

1.PNG

You could also download the pbix file to have a view:

https://www.dropbox.com/s/tm4duov3z5oqee4/Use%20a%20slicer%20to%20filter%20rows%20with%20Date%20From...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
kaushikd Member
Member

Re: Use a slicer to filter rows with Date From and Date To

You can bring 2 Calender table, one for ToDate and the other for FromDate. Manage the Relationship and connect the 3 Tables(1 Fact, 2 Calender Table) as shown below.

 

Now In the report drop 2 slicer, 1 Date Will come from one Calender Table and rest from the other.

 

 

Capture.JPG

 

kaushikd Member
Member

Re: Use a slicer to filter rows with Date From and Date To

One Slicer Property will be:-

Before and the other will be After.

Capture.JPG

dpombal Member
Member

Re: Use a slicer to filter rows with Date From and Date To

Using double Calendar Table is a good feature, however...how can I use an unique table of calendar, just thinking in other Fact tables with a Single Date Field .  I would require to filter from an unique  Calendar Table 

  • Below fact table with date from date to
  • Other fact tables with an unique date field

Any suggestions

Community Support Team
Community Support Team

Re: Use a slicer to filter rows with Date From and Date To

Hi @dpombal,

Based on my test, you could try to this formula:

Measure = 
IF (
    ISBLANK (
        CALCULATE (
            SUM ( 'Fact'[Operations] ),
            FILTER (
                ALL ( 'Fact' ),
                'Fact'[DateStart] <= MAX ( 'Table'[Date] )
                    && 'Fact'[DateEnd] >= MAX ( ( 'Table'[Date] ) )
            )
        )
    ),
    0,
    CALCULATE (
        SUM ( 'Fact'[Operations] ),
        FILTER (
            ALL ( 'Fact' ),
            'Fact'[DateStart] <= MAX ( 'Table'[Date] )
                && 'Fact'[DateEnd] >= MAX ( ( 'Table'[Date] ) )
        )
    )
)

Result:

1.PNG

You could also download the pbix file to have a view:

https://www.dropbox.com/s/aq4yhx4kxst5lyd/Use%20a%20slicer%20to%20filter%20rows%20with%20Date%20From...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dpombal Member
Member

Re: Use a slicer to filter rows with Date From and Date To

After adding customer as filter on the report this measure fails

Check report pbix here

 

https://1drv.ms/u/s!Am7buNMZi-gwnUlHVpIXvlEqjngE

Community Support Team
Community Support Team

Re: Use a slicer to filter rows with Date From and Date To

Hi @dpombal,

Based on my test, you could add two calculated columns in the calender table:

Column = RELATED('Fact'[Customer])
New = 
VAR LastNonBlankDate =
    CALCULATE (
        LASTNONBLANK ( 'Table'[Date], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && NOT ( ISBLANK ( 'Table'[Column]) )
        )
    )
RETURN
    CALCULATE (
        MAX( 'Table'[Column] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Date] = LastNonBlankDate )
    )

 

Result:

1.PNG

You could also download the pbix file to have a view:

https://www.dropbox.com/s/tm4duov3z5oqee4/Use%20a%20slicer%20to%20filter%20rows%20with%20Date%20From...

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Use a slicer to filter rows with Date From and Date To

Hi @dpombal,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.