locked
DAX: IF statement with compared dates by unique value RRS feed

  • Question

  • Hello, 

    Fairly simple question.  I have two tables ('Submission' and 'Date Key').  Both are linked via [Qtr] field.  

    I'm trying to create a calculated column in my 'Submission' table, which identifies whether the 'Submission'[Submit Date] column falls after the 'Date Key'[Close Date] column, and the [Qtr] fields have to match as well.   The calculated column will be called [On Time]

    Submission table:

    ID Submit Date Qtr
    112233 3/17/2017 2017Q1
    121213 12/19/2016 2016Q4
    332211 6/30/2016 2016Q2
    131211 9/24/2016 2016Q3

    Date Key table

    Qtr Open Date Close Date
    2016Q2 6/18/2016 6/24/2016
    2016Q3 9/16/2016 9/22/2016
    2016Q4 12/18/2016 12/24/2016
    2017Q1 3/16/2017 3/22/2017

    My Submission table should look like this:

    ID Submit Date Qtr On Time
    112233 3/17/2017 2017Q1 Yes
    121213 12/19/2016 2016Q4 Yes
    332211 6/30/2016 2016Q2 No
    131211 9/24/2016 2016Q3 No

    Do I use an IF statement for this?

    Thanks, 

    UG1


    • Edited by undergrads1 Tuesday, May 2, 2017 12:58 PM Font errors - text didn't show up
    Tuesday, May 2, 2017 12:54 PM

Answers

  • Hi undergrads1,

    Please create relationship in Power Pivot model.

    Then create a calculated column using the formula.

    =IF(Submission[Submit Date]<=RELATED(DateKey[Close Date]),"Yes","No")

    You will get the expected result, please see the following expected result.



    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, May 3, 2017 2:52 AM

All replies

  • Hi undergrads1,

    Please create relationship in Power Pivot model.

    Then create a calculated column using the formula.

    =IF(Submission[Submit Date]<=RELATED(DateKey[Close Date]),"Yes","No")

    You will get the expected result, please see the following expected result.



    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, May 3, 2017 2:52 AM
  • That's it!  The RELATED function is what I needed.  

    Thanks, Angela!

    ~UG1

    Wednesday, May 3, 2017 3:11 AM
  • So "Open Date" never enters into the calculations?
    Wednesday, May 3, 2017 3:21 AM
  • In my specific situation, no.  Because all submissions are not possible prior to the "Open Date" in my data source.  So, it will never be an issue.   :-)
    Wednesday, May 3, 2017 3:29 AM