Multiple date fields in Power BI

[exoticdisease]

Hi

I wish to show both a transaction date and an invoice date column against some spend data.  I have the spend data, along with invoice date key and transaction date key in one table which has a relationship to the date table via a date key.  I am told that I can use the date key to connect to both the invoice date key and transaction date key but I need to do this simultaneously to show the two columns simultaneously, I believe.

I've been told to create a new relationship between the two tables using the invoice date key and the date key, make it inactive, then create a measure using sum(spend) and userelationship() with the new relationship but couldn't get this to produce meaningful results.

Any advice would be very helpful!

[exoticdisease]

These are the two tables in question with the fields and relationship visible.

[exoticdisease]

Noone can help?  I just want three columns - invoice date, transaction date and spend... in my head, it should be easy!  Each invoice has one invoice date and one transaction date (e.g. paid date) so there's no issue with many to many relationships or anything...

Thanks

Rob

[v-jiascu-msft]

Hi Rob,

It should be easy. How do you want to show them up? If you just want to put them together, you can drag them together. But I don't think that's what you want.

Best Regards,

Dale

[exoticdisease]

Hi Dale

The problem is that I need to connect both s_keys to the date table to make them recognisable as dates rather than just strings of numbers (e.g. the keys).  But you can't have two relationships between one table active at once so I can't display both sets of dates?  I think..

Thanks

Rob

[exoticdisease]

Like the below - not much good.  Can only display one set of dates at a time because Power BI tells me that I cannot have two active relationships between the spend table and the date table.

[exoticdisease]

Hi again Dale

I tried your solution of using the hierarchy to create the two skeys together but you can't connect tables together with a hierarchical field so it wouldn't help in this case.  I think perhaps I need to explain the problem more clearly.

In the spend table, I don't have dates, I have date skeys.  The skeys are just unique identifiers for a date e.g. 1 is 01/01/2000 etc.  In this case, I have two fields of date skeys - invoice date skey and transaction date skey.  Each of them are just numbers which can be converted into dates using the date table.

In the date table, I have dates and also a field called date skey which is the connecting field to the spend table.  So I can either connect the two tables like this:

Invoice date skey -----> Date skey

or

Transaction date skey ----> Date skey

Then if I want to display the dates, I use the date table's other field (e.g. month, month year, full date etc).

BUT using this method, it is not possible to display both the transaction date and the invoice date, it's an either/or choice.

[exoticdisease]

https://community.powerbi.com/t5/Desktop/Linking-2-or-more-dates-to-one-Master-Calendar-USERELATIONS...

I found it!  USERELATIONSHIP.  However, I still can't get power bi to display an invoice number and the two dates associated with that invoice number - invoice date and transaction date.  Each invoice number should have one invoice date and one transaction date but I can't create a USERELATIONSHIP for date fields...

I've written this so far:

Invoice Date = calculate([_01_TotalSpendAllDivisions],userelationship('SPENDING FactLedgerData_New'[invoiceDate_sKey],'DIM DIMDate'[Date_SKey]))

Which allows me to show the spend at a certain invoice date but it won't allow me to show both the invoice date and transaction date for an invoice number and I cannot work out how to do that!

https://community.powerbi.com/t5/Desktop/Linking-2-or-more-dates-to-one-Master-Calendar-USERELATIONS...

[dedelman_clng]

Try creating a measure for the invoice date and putting that in your visual. Please note I haven't tested this.

Inv Date =
CALCULATE (
    MIN ( Date_SKey ),
    USERELATIONSHIP ( 'SPENDING FactLedgerData_New'[invoiceDate_sKey], 'DIM DIMDate'[Date_SKey] )
)

Otherwise you may want to look at the source data to see if Invoice Date can be sent to PowerBI as a date instead of an integer.

Hope this helps

David