locked
FIRSTDATE depending on different column RRS feed

  • Question

  • Hi there,

    I was trying all day to solve an issue but I can't get my head around it. I am trying to evaluate two different marketing campaigns from two different years. Therefore I want to check which campaign generated how much revenue in the first 7, 30, 90 and 180 days. I have a flat transaction table that contains amongst others the column "campaign" and "transaction date".

    My aim is now to look for the lowest date depending on the campaign (i.e. start date of the campaign). Subsequently I would add the different time intervals to the transaction date of each transaction and use an IF function to write a string (for example "Revenue 7") into a calculated column so that I can use it as a dimension in the pivot table to show the respective revenue.

    I would be really thankful if somebody has an answer to this as it drives me nuts... I must admit that I am pretty new to power pivot. I have some basic understanding of SQL but that didn't help so far.

    Thanks,

    Stefan




    Thursday, December 4, 2014 2:57 PM

Answers

  • You're doing something similar to what you did above. You are wrapping your reference to the date *and* your addition of 7 in the parentheses for the MIN(). Please look carefully at the code I posted and the code you have posted. 

    Thinking logically about what we want to do:

    1. Find the minimum of [Transaction Date].
    2. Add 7 to this value.
    3. Test whether the [Transaction Date] on the current row in consideration in FILTER() is less than the value computed in 1 and 2.

    Thus, we must find the minimum:

            MIN( NewsCampaign[Transaction Date] )
    Then we add 7:
            MIN( NewsCampaign[Transaction Date] ) + 7
    But, we want to perform this calculation before we do the comparison, thus:
            ( MIN( NewsCampaign[Transaction Date] ) + 7)
    Which then fits into the full FILTER() predicate as follows:
            , NewsCampaign[Transaction Date] <= ( MIN( NewsCampaign[Transaction Date] ) + 7 )
    It is much easier to read and debug functions if you follow some sort of formatting convention. DAX Formatter is quite good and will automatically provide indentation that makes a function much more readable.
    • Proposed as answer by Charlie Liao Tuesday, December 9, 2014 3:27 PM
    • Marked as answer by StefanDrescher Wednesday, December 10, 2014 8:46 AM
    Monday, December 8, 2014 5:00 PM

All replies

  • I have now tried to use a pivot measure instead. It looks like this:

    =CALCULATE(SUM(NewsCampaign[EUR]), FILTER(NewsCampaign, (NewsCampaign[Transaction Date]<=FIRSTDATE(NewsCampaign[Transaction Date]+7))))

    However this gives me strange numbers. They are much too low.

    When I change it to the follwoing just to see how it bahaves it gives me the total amount, so the filter seems not work. Looks like a logical mistake but I can't find it.

    =CALCULATE(SUM(NewsCampaign[EUR]), FILTER(NewsCampaign, (NewsCampaign[Transaction Date]=FIRSTDATE(NewsCampaign[Transaction Date]))))

    Any ideas?

    Thanks,

    Stefan







    Monday, December 8, 2014 3:49 PM
  • Just use MIN():

    MySum:=
    CALCULATE(
        SUM( NewsCampaign[EUR] )
        , FILTER( 
            NewsCampaign
            , NewsCampaign[Transaction Date] <= ( MIN( NewsCampaign[Transaction Date] ) + 7 )
        )
    )

    This works for me using your sample in the original post when I build a pivot table with [Campaign] on rows and this measure in the Values section.

    • Proposed as answer by Michael Amadi Tuesday, December 9, 2014 9:40 AM
    Monday, December 8, 2014 4:21 PM
  • Hey Greg,

    thanks for your answer!

    I used MIN

    =CALCULATE(SUM(NewsCampaign[EUR]), FILTER(NewsCampaign,NewsCampaign[Transaction Date] <= MIN(NewsCampaign[Transaction Date] + 7)))

    Table looks like this:

    Now I get the error message, that the MIN function only accepts one argument.

    Thanks,

    Stefan

    Monday, December 8, 2014 4:51 PM
  • You're doing something similar to what you did above. You are wrapping your reference to the date *and* your addition of 7 in the parentheses for the MIN(). Please look carefully at the code I posted and the code you have posted. 

    Thinking logically about what we want to do:

    1. Find the minimum of [Transaction Date].
    2. Add 7 to this value.
    3. Test whether the [Transaction Date] on the current row in consideration in FILTER() is less than the value computed in 1 and 2.

    Thus, we must find the minimum:

            MIN( NewsCampaign[Transaction Date] )
    Then we add 7:
            MIN( NewsCampaign[Transaction Date] ) + 7
    But, we want to perform this calculation before we do the comparison, thus:
            ( MIN( NewsCampaign[Transaction Date] ) + 7)
    Which then fits into the full FILTER() predicate as follows:
            , NewsCampaign[Transaction Date] <= ( MIN( NewsCampaign[Transaction Date] ) + 7 )
    It is much easier to read and debug functions if you follow some sort of formatting convention. DAX Formatter is quite good and will automatically provide indentation that makes a function much more readable.
    • Proposed as answer by Charlie Liao Tuesday, December 9, 2014 3:27 PM
    • Marked as answer by StefanDrescher Wednesday, December 10, 2014 8:46 AM
    Monday, December 8, 2014 5:00 PM
  • Thank you so much! It works perfectly. I wasn't aware of the two brackets and the dax formatter is really helpful to recognize this!
    Wednesday, December 10, 2014 8:46 AM