This has been doing my head in a little.
I have a Date Dimension Table and a Fact Table with multiple Date Columns.
I wish to find an arbitary total, on the condition that DATE1 ≤ DATE2 + 90 Days
Paid Invoices within 90 Days = CALCULATE([Collections Total], FILTER(FactCredits,FactCredits[FKCreditDate] <= FactCredits[FKInvoiceDate]+90))
This doesn't work because the colums are being treated as numbers. I can't work out how to incorperate USERELATIONSHIP so I can compare the two dates.
Any help appreciated!
What problem are you facing? What result are you expecting and what do you actually get. Share the link from where i can download your file. Also, show the expected result there.
Please confirm your [FKCreditDate] and [FKInvoiceDate] are date type, then try the following formula and check if it works fine.
Paid Invoices within 90 Days = CALCULATE ( [Collections Total], FILTER ( FactCredits, FactCredits[FKCreditDate] <= DATE ( YEAR ( FactCredits[FKInvoiceDate] ), MONTH ( FactCredits[FKInvoiceDate] ), DAY ( FactCredits[FKInvoiceDate] ) + 90 ) ) )
The problem I'm facing is both FKInvoiceDate and FKCreditDate are not date columns they're just INT stored as YYYYMMDD, they have a relationship with SKDate in my DimDate table (one active, one inactive)
I want to use both FKInvoiceDate and FKCreditDate in a measure or calculated column, so I need to use USERELATIONSHIP somewhere.
Hope this makese sense I'm unable to upload this as it's work related.
USERELATIONSHIP is a bit harder to use in a calculated column. Instead, I would use just RELATED for one of the two columns (the one with the active relationship) and go for LOOKUPVALUE for the second one.
In this way, you can retrieve the two dates and perform the math.
You can find a detailed description of the scenario here: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Have fun with DAX!
Hello @AlbertoFerrari! I recently received your Definitive Dax book, obviously not far enough through yet!
Thanks for pointing me in this direction, because the matter was urgent we solved the issue in SQL before building the model. I'll be recreating the issue in a test environment and will update this thread accordingly.