cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User I
Super User I

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@faacq one of the columns in a relationship must have unique values. A month name column will have the same month name appear at least 28 times. Date tables are normally linked to fact tables via the unique date column. All other columns are supplemental info about the date in question, not meant for creating relationships.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
faacq Frequent Visitor
Frequent Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Thank you for the reply. I probably do not undestand the purpose of having a date table. I was expecting after establishing the link between my fact table and the date table, to use the slicers to filter my data in many different ways.

If establishing the relation is not needed, how can I use the date table to slice my data?

 

Super User I
Super User I

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@faacq you understand the purpose correctly, but not the method. You're trying to create a relationship with a month column, which is incorrect. You're supposed to create a relationship between a date column in your fact table and the main date column in the date table. Then you could use the month column as a slicer.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
faacq Frequent Visitor
Frequent Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

You are right, using the date column I managed to establish the relation. When I try to slice my data It seems that the date table does not have any effect. My fact table has a column with "Month Year" info, but still I'm not able to slice my data with the columns available in the date table.

 

1.PNG

mbdtz Regular Visitor
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@faacq I think your problem will be solved if you just turn the date column in your data to a date, eg. if it is 201601 just change it to 1/1/2016

 

In terms of using it in the slicer, make sure you pull the date column from the date table into the slicer, not the date column from the data

 

let me know if that helps 🙂

faacq Frequent Visitor
Frequent Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

It worked, I established the relation between the date column in date.dim table and the date column in my fact table. I modified the content on the date column in my fact table from "Month" "year" to 01.01.2016.  I update the data type in PowerBI to (MMM yyyy)

Thank you for your help

Rsanjuan Member
Member

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Hi,

 

Now that I have this date table, I am having trouble actually using it.  How can I calculate a rolling calculation, so that it will count the number of an event 60 days from today's date?  Also, how to count the number of events in the future?

 

 

Super User III
Super User III

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Nice trick!

Just came across this blogpost using ADDCOLUMN allowing you to "script" the table-creation, so no need to create every single column manually: http://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps...

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Thanks!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Highlighted
mbdtz Regular Visitor
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

@ImkeF thanks so much! awesome 😄

amysmith Regular Visitor
Regular Visitor

Re: Possible Tip/Trick: Dynamic Date Dimension Table

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors