cancel
Showing results for
Did you mean:
Solution Sage

## Date Slicer

I have a date table and my fact table.

My fact table has Due Dates.

I want to have a slicer of dates that will filter the due dates of the table i have.

for example:

Slicer date is 31/08/2019

what will show are all the due dates from 31/08/2019 backwards.

I also want to see the delay days. Delay days will be datediff of selected date from slicer and due date

Slicer: 31/08/2019

Table:

Doc No.        Due Date           Delay Days

ABC-01       23/07/2019             39

ABC-02       30/07/2019             32

ABC-03       15/08/2019             16

ABC-04       22/08/2019             9

ABC-05       30/08/2019             1

ABC-06       31/082019              0

If I want to go back, like this:

Slicer: 20/08/2019

Table:

Doc No.        Due Date           Delay Days

ABC-01       23/07/2019             28

ABC-02       30/07/2019             21

ABC-03       15/08/2019              5

I know this can be done in power bi.

But I don't know how I will connect the date table to the fact table.

I will appreciate any help. Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Date Slicer

@mussaenda add following two measures and see if it works

```Is date before max date =
VAR __date = MAX( 'Calendar'[Date] )
RETURN
CALCULATE( COUNTROWS( 'Table 1' ), 'Table 1'[Due Date] <= __date)```
```Delay Days =
DATEDIFF( MAX( 'Table 1'[Due Date] ), MAX( 'Calendar'[Date] ), DAY ) * DIVIDE( [Is date before max date],[Is date before max date] )```

- Doc No

- Due Date

- Delay Days

and you will get the result

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

4 REPLIES 4
Super User IV

## Re: Date Slicer

@mussaenda add following two measures and see if it works

```Is date before max date =
VAR __date = MAX( 'Calendar'[Date] )
RETURN
CALCULATE( COUNTROWS( 'Table 1' ), 'Table 1'[Due Date] <= __date)```
```Delay Days =
DATEDIFF( MAX( 'Table 1'[Due Date] ), MAX( 'Calendar'[Date] ), DAY ) * DIVIDE( [Is date before max date],[Is date before max date] )```

- Doc No

- Due Date

- Delay Days

and you will get the result

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Solution Sage

## Re: Date Slicer

Hi @parry2k,

Should I create a relationship between the date table and the fact able using the due date and dates to create these?

Super User IV

## Re: Date Slicer

@mussaenda no you don't need that relationship

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Solution Sage

## Re: Date Slicer

Yay! Awesome it works!

Thank you!

But.. It only works on the table.

If I want to work it on the whole page like a filter?

I mean, The blank on the delay days meaure I want to hide on the whole page.

Can we convert it to a calculated column?

But, your mesaure is a big step forward for me. Thank you @parry2k !!

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors