Forum Discussion

exoticdisease's avatar
exoticdisease
Regular Visitor
8 years ago

Multiple date fields in Power BI

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...
Need Help
exoticdisease's avatar
exoticdisease
Regular Visitor
8 years ago

https://community.powerbi.com/t5/Desktop/Linking-2-or-more-dates-to-one-Master-Calendar-USERELATIONSHIP/td-p/9316

 

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-USERELATIONSHIP/td-p/9316

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
8 years ago

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