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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RYU209
Helper III
Helper III

How to compare built in time intelligence date hierarchies with custom date hierarchies?

Hello,

I have a date table with a relationship to my main transaction table via a Project Start Date. Because the relationship is built on the Project Start Date, the Project Start Date does not have a built in date hierachy and instead I created my own. I used YEAR() to extract the year number and MONTH() to extract the month number in two calculated columns and added them to the new hierarchy.

Now I want to compare the extracted Year and Month to the built-in date hierarchy in my date table in a DAX measure; something along the lines of IF(Date Table[Date].[Month] = Main Table[ProjectStartDate].[Custom Hierarchy Month], ...). How would I go about this? 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@RYU209 it should work if you are using userelationship  and that's the way to go. Can you make sure the project completion date doesn't have time value, it is purely a date. Can you share your measure expressions?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

12 REPLIES 12
parry2k
Super User
Super User

@RYU209 it should work if you are using userelationship  and that's the way to go. Can you make sure the project completion date doesn't have time value, it is purely a date. Can you share your measure expressions?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k ,

I forgot to change the data type to Date - that fixed it. Thanks!

parry2k
Super User
Super User

@RYU209 you just need to sum the amount (or count measure) whatever that calculation is and in the visualization use year/month from the date dimension and the measure.

 

I'm assuming you already have a relationship between the new calendar dimension and the transaction table.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@RYU209 what calculation do you want to perform, provide those details.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hello @parry2k,

I need to count the amount of projects that were started at a Year-Month granularity that works in a visualization with Date Table[Date].[Month] as the X-axis. 

You just need a count measure, and then grab whatever date dimensions you want to show it by from the date_dim table. The date table I sent you a link to has many, many invaluable fields!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Hi @audreygerred,

Thank you, actually I didn't mention that I also want to report on the count of Projects that have been completed using a "Project Completion Date" column but I want the count to be shown alongside the Project Started count using the same X-Axis. 

Currently I have an activate relationship to the Date table via Project Start Date and an inactive relationship to the Date table as well but with the Project Completion Date. I'm trying to use USERELATIONSHIP but the problem is that the count on Project Completions is showing a "blank" date when using the date table. 

Try out this method: DAX measure with start AND end date for Power BI - YouTube




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





parry2k
Super User
Super User

@RYU209 what you are working with dates, everything has to be driven from the date dimension which you have already into your model. You can learn more about all it here: 

 

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

audreygerred
Super User
Super User

Since you have a date table, you should create the hierarchy in the date table and utilize the fields from there instead of your fact table. Here is a blog I wrote about the date table I love from SLBI: http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/ 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Hi @audreygerred ,

I will use the Date table in my visualizations but currently, I need to perform some calculations that compare at a Year-Month level. 

The date_dim table I suggested has fields that will accomodate this, it's very well designed.😁




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.