Date Slicer

[mussaenda]

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!

[parry2k · Accepted solution]

@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] )

in table visual add following

- Doc No

- Due Date

- Delay Days

and you will get the result

[mussaenda]

Hi @parry2k,

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

[parry2k]

@mussaenda no you don't need that relationship

[mussaenda]

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