Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Time intelligence filtering

Hello,   I am trying to create a distinct count, and later on a list of all opportunities which were at a certain stage, this time a week ago. In this case, I am interested in any opportunities whi...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    HI Anonymous,

    Yes, these conditions are linked with 'AND' logic(calculate function filters also use the 'AND' logic) so they are required to be matched at the same time. 

    Do you mean they are two group of conditions that are linked with OR logic? If that is the case, you can try to use the following formulas:

    Forecast Commit opps last week =
VAR currDate =
    MAX ( 'Dates'[Date] )
VAR OneWeekAgo =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 7 )
RETURN
    CALCULATE (
        [Opportunities],
        -- This is a measure which is just a DISTINCTCOUNT of the opportunity ID
        FILTER (
            ALLSELECTED ( 'Opportunity Field History' ),
            OR (
                AND ( [Valid From] <= OneWeekAgo, [ValidTo] > OneWeekAgo )
                    && [NewValue] = "Commit",
                AND (
                    YEAR ( [New Close Date] ) = YEAR ( TODAY () ),
                    MONTH ( [New Close Date] ) = MONTH ( TODAY () )
                )
            )
        )
    )

    IF the above does not help, can you please some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

    How to Get Your Question Answered Quickly  

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
4 years ago

HI Anonymous,

You can try to use the following measure formula if it works:

Forecast Commit opps last week =
VAR currDate =
    MAX ( 'Dates'[Date] )
VAR OneWeekAgo =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 7 )
RETURN
    CALCULATE (
        [Opportunities],
        -- This is a measure which is just a DISTINCTCOUNT of the opportunity ID
        FILTER (
            ALLSELECTED ( 'Opportunity Field History' ),
            AND ( [Valid From] <= OneWeekAgo, [ValidTo] > OneWeekAgo )
                && [NewValue] = "Commit"
                && AND (
                    YEAR ( [New Close Date] ) = YEAR ( TODAY () ),
                    MONTH ( [New Close Date] ) = MONTH ( TODAY () )
                )
        )
    )

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello,

     

    Thank you for taking the time to post this support. I still get a blank measure here - may this be because && syntax is filtering on all three criteria? If I do that, it does get a blank; when you filter on new value = commit, the new close date will never be in this month as this is a blank field. 

     

    I need a way to filter on oneweekago AND commit as well as oneweekago AND new close date = this month. 

     

    I only want to distinct count opportunity IDs which were at both these states one week ago. 

     

    Thanks

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      HI Anonymous,

      Yes, these conditions are linked with 'AND' logic(calculate function filters also use the 'AND' logic) so they are required to be matched at the same time. 

      Do you mean they are two group of conditions that are linked with OR logic? If that is the case, you can try to use the following formulas:

      Forecast Commit opps last week =
VAR currDate =
    MAX ( 'Dates'[Date] )
VAR OneWeekAgo =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( currDate ) - 7 )
RETURN
    CALCULATE (
        [Opportunities],
        -- This is a measure which is just a DISTINCTCOUNT of the opportunity ID
        FILTER (
            ALLSELECTED ( 'Opportunity Field History' ),
            OR (
                AND ( [Valid From] <= OneWeekAgo, [ValidTo] > OneWeekAgo )
                    && [NewValue] = "Commit",
                AND (
                    YEAR ( [New Close Date] ) = YEAR ( TODAY () ),
                    MONTH ( [New Close Date] ) = MONTH ( TODAY () )
                )
            )
        )
    )

      IF the above does not help, can you please some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

      How to Get Your Question Answered Quickly  

      Regards,

      Xiaoxin Sheng