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.
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
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:
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!
Go to Solution.
@mussaenda add following two measures and see if it works
Is date before max date =
VAR __date = MAX( 'Calendar'[Date] )
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
Proud to be a Datanaut! Appreciate your Kudos Feel free to email me with any of your BI needs.
Should I create a relationship between the date table and the fact able using the due date and dates to create these?
@mussaenda no you don't need that relationship
Yay! Awesome it works!
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 !!
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.