Forum Discussion

SMBM's avatar
SMBM
Frequent Visitor
2 years ago
Solved

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.



Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    2 years ago

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

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    2 years ago

    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 ()
)
    • SMBM's avatar
      SMBM
      Frequent Visitor
      2 years ago

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