Custom date hierarchy for multiple columns

[IvanS]

Hello,

I am having issue with mirroring the created custom date hierarchy (including week nr.) from calculated Date Table for more than 1 date column in the FACT table.

Example:

I have FACT_Invoice table with following columns:

- Invoice Nr.

- Posting Date

- Due Date

- Payment Date

Currently, I have my DateTable connected to "Posting Date" column which allows me to use the created custom hierarchy incl. weeks. I have request to visualise also "Due Date" and "Payment Date" on graph on weekly basis. Is there any way how to connect the other 2 columns to the date table? I am not very skilled in USERELATIONSHIP but from the structure of the function, this looks like creating virtual relationship when performing calculation. Here, I do not need to perform any calculation, just to simply visualise the data.

Thank you for any suggestions!

Ivan

[amitchandak · Accepted solution]

@IvanS , Make sure you calendar has all columns you require. Then have use userelationship

Assume due and payment are inactive joins

calculate( SUM(Fact_invoice[Amount]),USERELATIONSHIP ('Fact_invoice'[Due Date], 'Date'[Date]))

calculate( SUM(Fact_invoice[Amount]),USERELATIONSHIP ('Fact_invoice'[Payment Date], 'Date'[Date]))

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[IvanS]

Hi @amitchandak ,

and what if I need average, max or min. Do I need to create measure for each of those scenarios? Wouldn't it make sense to create duplicates of Date Table that will be connected for other types of date columns?