cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mussaenda Senior Member
Senior Member

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

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

in table visual add following

 

- Doc No

- Due Date

- Delay Days

 

and you will get the result






Did I answer your question? Mark my post as a solution.

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





4 REPLIES 4
Super User
Super User

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

in table visual add following

 

- Doc No

- Due Date

- Delay Days

 

and you will get the result






Did I answer your question? Mark my post as a solution.

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





mussaenda Senior Member
Senior Member

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

Re: Date Slicer

@mussaenda no you don't need that relationship






Did I answer your question? Mark my post as a solution.

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





mussaenda Senior Member
Senior Member

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 268 members 2,976 guests
Please welcome our newest community members: