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