cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kashinoda Regular Visitor
Regular Visitor

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

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!

 

 

7 REPLIES 7
Super User
Super User

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

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.

v-huizhn-msft Super Contributor
Super Contributor

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

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

Kashinoda Regular Visitor
Regular Visitor

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

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.

Super User
Super User

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

Hi,

 

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

Highlighted

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

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

Alberto Ferrari - SQLBI
v-huizhn-msft Super Contributor
Super Contributor

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

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

Kashinoda Regular Visitor
Regular Visitor

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

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.