Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mussaenda
Community Champion
Community Champion

Date Slicer using Calculated Column

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.

2 REPLIES 2
TomMartens
Super User
Super User

Hey @mussaenda ,

 

please be aware that this requirement can not be resolved using calculated columns, this is due to the following:

  • calculated columns will only be "re-calculated" during data refresh
  • the change of a slicer value will not issue a re-calculation of calculated columns

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.