URGENT: linking multiple date columns from fact table to date table

[cherylakrols94]

Dear Helper,

Please can you assist me with my problem that needs to be solved before end of business day? 

I have 2 date columns in my fact table: Invoice Date + Order Received, that I need to be able to use for calculations.

I need to be able to create a clustered column chart, comparing how many orders were opened and processed each month (Order Received) vs. Orders Invoiced (Invoice Date). The unique value in each line is column OR no.

If I create a relationship from both of these columns to the date table then 1 becomes inactive. I have also already tried to use the USERELATIONSHIP DAX but I am unsuccessful.

I have also already tried creating a duplicate date table to be able to have 2 different active relationships to a date table for each date column. But then the X-axis dates duplicate in my visual.

What is your advice?

(I am self taught thru YouTube and have only been actively using Power BI for a few months now so please talk to me like I'm 5 😛 )

[Anonymous]

Hi

You can create Date table & create relationship between your invoice date & Order date column with Date Table Date column.

one relationship should be active & another will be inactive. Then you can use below formula for inactive relationship.

Order_Count =

            CALCULATE(COUNTROWS(OrderVsInvoice),

                      USERELATIONSHIP(Dates[Date],OrderVsInvoice[Ordered Received Date])

            )

[miTutorials]

If you have not sorted this out yet, check out my video below, I have explained this in detail.

(10) Dealing with Multiple Date Fields to bring values from same time period in Power BI | MiTutoria...

[Anonymous]

Hi, @cherylakrols94 

Based on your information, I create a sample table:

You mentioned that you create a relationship between the Invoice Date and Order Received columns and the date table, but one of the relationships becomes inactive. This is expected behavior for Power BI because there can only be one active relationship between two tables.

Create a new date table, use the following dax:

Date = CALENDAR("2024-1-1", "2024-12-31")

Then create new measures, here is the DAX expression:

OrdersInvoiced = CALCULATE(COUNT('Table'[OR no]), USERELATIONSHIP('Table'[Invoice Date], 'Date'[Date]))

OrdersReceived = CALCULATE(COUNT('Table'[OR no]), USERELATIONSHIP('Table'[Order Received], 'Date'[Date]))

Add a clustered column chart to your report. Use the Date column in the date table as the X-axis and put measures in visual.

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1 · Accepted solution]

Hi @cherylakrols94  - Challenging part is that you cannot use two active relationships between the date table and the fact table simultaneously. 

Create a Date Table (if you don't already have one).
Your Date Table should have a continuous range of dates and include columns like Year, Month, Day, etc.
Mark this table as a Date Table in Power BI (Modeling > Mark as Date Table).

date calculated table: 

DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
"Year", YEAR([Date]),
"Month", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)

Create one relationship from your Invoice Date column--> Date Table (active relationship).
Create another relationship from your Order Received column --> Date Table (inactive relationship). Use the USERELATIONSHIP function to activate the inactive relationship when needed.

orders Received =
CALCULATE(
DISTINCTCOUNT('FactTable'[OR No]),
USERELATIONSHIP('FactTable'[Order Received], 'DateTable'[Date])
)

create another measure for orderInvoiced

Orders Invoiced =
DISTINCTCOUNT('FactTable'[OR No])

Check these and let me know.

[cherylakrols94]

Hello @rajendraongole1,

Thank you! Integrating the distinct count with the use relationship function is what did it!

your help is much appreciated

[suparnababu8]

Hi @cherylakrols94  

If you facing problem still after using of USERELATIONSHIP DAX. If i assume you have Fact Table and DateTable. Pls follow below dax nad give a try.

Orders Received Measure:

Orders Received = 
CALCULATE(COUNTROWS('FactTable'),
    USERELATIONSHIP('DateTable'[Date], 'FactTable'[Order Received]),
    REMOVEFILTERS('DateTable'))

Orders Invoiced Measure:

Orders Invoiced = 
CALCULATE(COUNTROWS('FactTable'),
    USERELATIONSHIP('DateTable'[Date], 'FactTable'[Invoice Date]),
    REMOVEFILTERS('DateTable'))

After creation of this mesures, add the Date column from your date table to the X-axis of the clustered column  chart  and add the two measures (Orders Received and Orders Invoiced) to the Values field. 

Make sure your date table has a continuous range of dates and is marked as a date table.

Let me know if it works. Thanks