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!

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

Forum Discussion