Forum Discussion
Multiple date fields in Power BI
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
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
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.
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.
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!