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 which were Commit and the close date is in the current month (TODAY()), but for one week back.

 

I have built a temporal table which has a valid from and to date, using DAX to work out the next change date for that field. Based on this, I am trying to write a measure which will give a distinct count for all opportunity IDs which were valid one week ago, but both in a status of Commit and also with a close date of this month. 

 

It isnt working at the moment, and i know why - because I am filtering on three columns at the same time (valid from/to, Commit and also Close Date), and this is returning a blank.

 

Can anyone recommend a way to change this so the distinct count brings back all opportunities which were commit and due to close this month, for a week ago? I have tried to create some AND statements, however Filtering isnt allowed for TRUE/FALSE expressions, which brings back and error.

 

The measure:

 

Forecast Commit opps last week =
VAR LastWeekTable = DATEADD('Dates'[Date],-7,DAY)
VAR OneWeekAgo = MAXX(LastWeekTable,[Date])
RETURN
CALCULATE([Opportunities], -- This is a measure which is just a DISTINCTCOUNT of the opportunity ID
FILTER('Opportunity Field History',
AND(
'Opportunity Field History'[Valid From]<=OneWeekAgo,
'Opportunity Field History'[ValidTo]>OneWeekAgo
)
),
FILTER('Opportunity Field History','Opportunity Field History'[NewValue]="Commit"),
FILTER('Opportunity Field History',
AND(
YEAR('Opportunity Field History'[New Close Date])=YEAR(TODAY()), -- New Close date is a column which identifies field changes to a date, and converts it to a DATEVALUE
MONTH('Opportunity Field History'[New Close Date])=MONTH(TODAY())) -- New Close date is a column which identifies field changes to a date, and converts it to a DATEVALUE
))
 
A sample of the table:
 
OpportunityIdValid FromValidToFieldOldValueNewValue
ABC12303/11/2020 09:3418/12/2020 10:07CloseDate01/10/202001/01/2021
ABC12308/12/2020 12:0627/11/2021 22:13Amount2500016650
ABC12318/12/2020 10:0710/02/2021 11:07CloseDate01/01/202101/02/2021
ABC12318/12/2020 10:0731/12/9999 00:00ForecastCategoryNamePipelineCommit
ABC12310/02/2021 11:0708/03/2021 12:24CloseDate01/02/202101/03/2021
ABC12308/03/2021 12:2424/03/2021 08:17CloseDate01/03/202120/03/2021
ABC12324/03/2021 08:1726/04/2021 09:38CloseDate20/03/202109/04/2021
ABC12326/04/2021 09:3828/04/2021 08:03CloseDate09/04/202130/04/2021
ABC12328/04/2021 08:0317/05/2021 11:49CloseDate30/04/202115/05/2021
ABC12317/05/2021 11:4906/08/2021 13:07CloseDate15/05/202115/08/2021
ABC12306/08/2021 13:0730/10/2021 11:53CloseDate15/08/202115/10/2021
ABC12330/10/2021 11:5329/11/2021 08:23CloseDate15/10/202120/11/2021
ABC12327/11/2021 22:2631/12/9999 00:00Amount016650
ABC12327/11/2021 22:1327/11/2021 22:26Amount166500
ABC12329/11/2021 08:2321/12/2021 10:30CloseDate20/11/202115/12/2021
ABC12310/01/2022 10:3028/03/2022 07:05CloseDate15/01/202215/03/2022
ABC12321/12/2021 10:3010/01/2022 10:30CloseDate15/12/202115/01/2022
ABC12328/03/2022 07:0531/12/9999 00:00CloseDate15/03/202230/04/2022

 

Any help appreciated! Thanks.

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

3 Replies

  • 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