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
SMBM
New Member

Creating Calculated Column with Related Table and DATEADD

I have a dimensions table that contains a list of all skus in a system.  One of the dimensions is the date that the sku was introduced.  It has a relationship with a fact table that has all transactions of all of those materials.  This includes the billing date.  I have two needs.  The first is a calculated column that records the revenue from a transaction if it happened after the date the sku was publicly introduced (we have transactions that happened before public introduction).  The second is a calculated column that records the revenue from a transaction if it happened within 1 year of the sku being publicly introduced.  For the first need, I'm using the following and it works exactly as expected:

 
REVENUE_AFTER_INTRODUCTION = IF(AND(RELATED('SKU Data'[INTRODUCTION_TYPE]) = "Public Release",RELATED('Material Data'[INTRODUCTION_DATE])<= 'Transactional Data'[BILLING_DATE]), 'Transactional Data'[REVENUE],blank())
 
 
For the second need, I can't figure out how to shift the INTRODUCTION_DATE by 12 months to compare it to the BILLING_DATE.  When I use DATEADD, it causes and error.  I have tried the following:

REVENUE_WITHIN_12_MONTHS_OF_INTRODUCTION = IF(AND(RELATED('Material Data'[INTRODUCTION_TYPE]) = "Public Release", AND(DATEADD(RELATED('Material Data'[INTRODUCTION_DATE]),12,MONTH)>= 'Transactional Data'[BILLING_DATE], RELATED('Material Data'[INTRODUCTION_DATE])<= 'Transactional Data'[BILLING_DATE])), 'Transactional Data'[REVENUE],blank())

The error there is that the first argument to DATEADD must specify a column.
 
If I swap it to put the DATEADD inside the related like this:

REVENUE_WITHIN_12_MONTHS_OF_INTRODUCTION = IF(AND(RELATED('Material Data'[INTRODUCTION_TYPE]) = "Public Release", AND(RELATED(DATEADD('Material Data'[INTRODUCTION_DATE]),12,MONTH))>= 'Transactional Data'[BILLING_DATE], RELATED('Material Data'[INTRODUCTION_DATE])<= 'Transactional Data'[BILLING_DATE])), 'Transactional Data'[REVENUE],blank())
 
I get the error that RELATED expects a fully qualified column reference as it's argument.
 
Is there any way to accomplish what I'm looking for via a calculated column?  
 
Thank you for any assistance.



1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@SMBM 

I think it should work if you just add 365 to the [Introduction Date].  Does this give you what you are looking for?

REVENUE_WITHIN_12_MONTHS_OF_INTRODUCTION =
IF (
    AND (
        RELATED ( 'Material Data'[INTRODUCTION_TYPE] ) = "Public Release",
        AND (
            RELATED ( 'Material Data'[INTRODUCTION_DATE] ) + 365 >= 'Transactional Data'[BILLING_DATE],
            RELATED ( 'Material Data'[INTRODUCTION_DATE] ) <= 'Transactional Data'[BILLING_DATE]
        )
    ),
    'Transactional Data'[REVENUE],
    BLANK ()
)

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

@SMBM 

I think it should work if you just add 365 to the [Introduction Date].  Does this give you what you are looking for?

REVENUE_WITHIN_12_MONTHS_OF_INTRODUCTION =
IF (
    AND (
        RELATED ( 'Material Data'[INTRODUCTION_TYPE] ) = "Public Release",
        AND (
            RELATED ( 'Material Data'[INTRODUCTION_DATE] ) + 365 >= 'Transactional Data'[BILLING_DATE],
            RELATED ( 'Material Data'[INTRODUCTION_DATE] ) <= 'Transactional Data'[BILLING_DATE]
        )
    ),
    'Transactional Data'[REVENUE],
    BLANK ()
)

Holy crap.  Talk about overcomplicating something with an elegantly simple solution.  This did the trick!  Thanks a bunch for the help.  Marked as solution!

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.