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

[dpombal]

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

Fact

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,

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

[v-danhe-msft]

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

[kaushikd]

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.

[kaushikd]

One Slicer Property will be:-

Before and the other will be After.

[dpombal]

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

[v-danhe-msft]

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:

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

[dpombal]

After adding customer as filter on the report this measure fails

Check report pbix here

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

[v-danhe-msft · Accepted solution]

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:

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