Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kashinoda
Advocate I
Advocate I

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

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

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
Microsoft Employee
Microsoft Employee

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.