Time intelligence filtering

[Anonymous]

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:

OpportunityId	Valid From	ValidTo	Field	OldValue	NewValue
ABC123	03/11/2020 09:34	18/12/2020 10:07	CloseDate	01/10/2020	01/01/2021
ABC123	08/12/2020 12:06	27/11/2021 22:13	Amount	25000	16650
ABC123	18/12/2020 10:07	10/02/2021 11:07	CloseDate	01/01/2021	01/02/2021
ABC123	18/12/2020 10:07	31/12/9999 00:00	ForecastCategoryName	Pipeline	Commit
ABC123	10/02/2021 11:07	08/03/2021 12:24	CloseDate	01/02/2021	01/03/2021
ABC123	08/03/2021 12:24	24/03/2021 08:17	CloseDate	01/03/2021	20/03/2021
ABC123	24/03/2021 08:17	26/04/2021 09:38	CloseDate	20/03/2021	09/04/2021
ABC123	26/04/2021 09:38	28/04/2021 08:03	CloseDate	09/04/2021	30/04/2021
ABC123	28/04/2021 08:03	17/05/2021 11:49	CloseDate	30/04/2021	15/05/2021
ABC123	17/05/2021 11:49	06/08/2021 13:07	CloseDate	15/05/2021	15/08/2021
ABC123	06/08/2021 13:07	30/10/2021 11:53	CloseDate	15/08/2021	15/10/2021
ABC123	30/10/2021 11:53	29/11/2021 08:23	CloseDate	15/10/2021	20/11/2021
ABC123	27/11/2021 22:26	31/12/9999 00:00	Amount	0	16650
ABC123	27/11/2021 22:13	27/11/2021 22:26	Amount	16650	0
ABC123	29/11/2021 08:23	21/12/2021 10:30	CloseDate	20/11/2021	15/12/2021
ABC123	10/01/2022 10:30	28/03/2022 07:05	CloseDate	15/01/2022	15/03/2022
ABC123	21/12/2021 10:30	10/01/2022 10:30	CloseDate	15/12/2021	15/01/2022
ABC123	28/03/2022 07:05	31/12/9999 00:00	CloseDate	15/03/2022	30/04/2022

Any help appreciated! Thanks.

[Anonymous]

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]

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 · Accepted solution]

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