cancel
Showing results for
Did you mean:
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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## 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:

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
Highlighted
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.

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.

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

Super Contributor

## 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:

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.
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

Super Contributor

## 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:

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.
Super Contributor

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

Hi @dpombal,

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.

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 6 members 3,632 guests
Recent signins: