cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mussaenda Solution Sage
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
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] )

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 🙂
Feel free to email me with any of your BI needs.





View solution in original post

4 REPLIES 4
Super User IV
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] )

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 🙂
Feel free to email me with any of your BI needs.





View solution in original post

mussaenda Solution Sage
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
Super User IV

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 🙂
Feel free to email me with any of your BI needs.





mussaenda Solution Sage
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 !!

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors