Using DAX operators on multiple Dates from the same Fact table?

[Kashinoda]

Hi Guys,

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

Example

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!

[AlbertoFerrari]

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!

Alberto Ferrari
http://www.sqlbi.com

[Kashinoda]

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.

[v-huizhn-msft]

Hi @Kashinoda,

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 )
    )
)

Best Regards,
Angelia

[Ashish_Mathur]

Hi,

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.

[Kashinoda]

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.

[v-huizhn-msft]

Hi @Kashinoda,

Have you resolved your issue?  Please refer the solution @AlbertoFerrari posted. If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia

[Ashish_Mathur]

Hi,

Create a dummy dataset and share the link of that dummy dataset.