Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a date table and my fact table.
My fact table has Due Dates and Posting 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!
**********************************
I have posted this one before and was solved using measures.
I am posting again as I am looking for a solution using columns.
Hey @mussaenda ,
please be aware that this requirement can not be resolved using calculated columns, this is due to the following:
As the value of "Delay Days" depends on a slicer value, this can not be used to as an input for a calculated column.
Please describe in more detail why you need this as a calculated column.
My assumption: you want to use "Delay Days" as a slicer or axis.
You may consider to provide a pbix file that contains some sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you use and xlsx file to create the sample data, do not forget to upload the xlsx as well.
Basically, this kind of requirements can be solved by creating a disconnected table that represents all the possible values of "Delay Days", and some little more complex measures that map for e.g. the "Doc-No" to this disconnected table.
Hopefully this provides some additional insights.
Regards,
Tom
Hi @TomMartens,
I understand your point. Thank you for explaining clearly.
I am getting frustrated here.
You are right, my plan was to use the delay days as filter.
here is the pbix link.
With a little explanation inside.
Thank you
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
81 | |
70 | |
49 |
User | Count |
---|---|
143 | |
124 | |
107 | |
60 | |
55 |