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
Microsoft

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

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

Microsoft

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

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

Microsoft

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

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

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

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)