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 transacti...
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 ()
)
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!