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

[RYU209]

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? 

[parry2k · Accepted solution]

@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?

[RYU209]

Hi @parry2k ,

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

[parry2k]

@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.

[parry2k]

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

[RYU209]

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. 

[audreygerred]

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!

[RYU209]

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. 

[audreygerred]

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

[parry2k]

@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

[audreygerred]

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/ 

[RYU209]

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. 

[audreygerred]

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