Using VAR in measure - adding a filter

[jhenscheid1]

Hello, I have the below measure in a report which is providing data on "Opened" accounts.  I am tying sales to this so we can understand how well we are doing each Fiscal week.  To provide a little background we began measuring the sales and opened accounts in week 9 (when the project started).  We are finding recently, however, that many accounts in a certain category were opened before week 9.  But for various reasons, the manager does not want to include them until week 47 (the week they were updated).  They are considered "National" accounts and are in a different table (Sales) than the Amount Ordered table (referenced in the measure below).

So my question is how to add a filter in the measure to exclude "National" accounts from being considered a new account.

Thanks

New Acct =

        VAR FirstPurchaseCustomers =

            ADDCOLUMNS (

                ALL ( Accounts[Account Number] ),

                "DateOfFirstBuy", CALCULATE ( MIN ( 'Amount Ordered'[Fiscal Week] ), ALLEXCEPT ( 'Amount Ordered', Accounts[Account Number] ) )

            )

        VAR NewAccts =

            FILTER (

                FirstPurchaseCustomers,

                CONTAINS ( VALUES ( 'Amount Ordered'[Fiscal Week] ), 'Amount Ordered'[Fiscal Week], [DateOfFirstBuy] )                

            )

        RETURN

            COUNTROWS (NewAccts)

[Anonymous]

Hi @jhenscheid1 ,

Please try:

New Acct =
VAR FirstPurchaseCustomers =
    ADDCOLUMNS (
        ALL ( Accounts[Account Number] ),
        "DateOfFirstBuy",
            CALCULATE (
                MIN ( 'Amount Ordered'[Fiscal Week] ),
                ALLEXCEPT ( 'Amount Ordered', Accounts[Account Number] )
            )
    )
VAR NewAccts =
    FILTER (
        FirstPurchaseCustomers,
        CONTAINS (
            FILTER (
                VALUES ( 'Amount Ordered'[Fiscal Week] ),
                'Amount Ordered'[Fiscal Week] > 9
            ),
            'Amount Ordered'[Fiscal Week], [DateOfFirstBuy]
        )
    )
RETURN
    COUNTROWS ( NewAccts )

If this doesn't work, please feel free to contact me.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[jhenscheid1]

Hello @Anonymous , thanks for the reply and input.  I think this is part of the solution.  By that I mean that I will need to filter data based on the fiscal week, but I also need to consider the "National" accounts, which are in a different table.  And I may not have been clear on what is needed.

So, I need to include new accounts that are not National, but exclude the National accounts.  I tried to add that to the filter, but I am thinking because that column is in a different table, it will not work. 

I tried something like:

New Acct =

        VAR FirstPurchaseCustomers =

            ADDCOLUMNS (

                ALL ( Accounts[Account Number] ),

                "DateOfFirstBuy", CALCULATE ( MIN ( 'Amount Ordered'[Fiscal Week] ), ALLEXCEPT ( 'Amount Ordered', Accounts[Account Number] ) )

            )

        VAR NewAccts =

            FILTER (

                FirstPurchaseCustomers,

                CONTAINS (

                    FILTER (

                        VALUES ( 'Amount Ordered'[Fiscal Week] ),

                        'Amount Ordered'[Fiscal Week] > 9 ,

                        FILTER('Sales Leads', 'Sales Leads'[Lead Type] <> "Nationwide Buy Group"),

                )

                 'Amount Ordered'[Fiscal Week], [DateOfFirstBuy] )                

            )

        RETURN

            COUNTROWS (NewAccts)

Hope that explains more of what I am looking to do.

Thanks!

[Anonymous]

Hi @jhenscheid1 ,

Please try:

New Acct = 
VAR FirstPurchaseCustomers =
    ADDCOLUMNS (
        ALL ( Accounts[Account Number] ),
        "DateOfFirstBuy",
            CALCULATE (
                MIN ( 'Amount Ordered'[Fiscal Week] ),
                ALLEXCEPT ( 'Amount Ordered', Accounts[Account Number] )
            )
    )
VAR NewAccts =
    FILTER (
        FirstPurchaseCustomers,
        CONTAINS (
            CALCULATETABLE (
                VALUES ( 'Amount Ordered'[Fiscal Week] ),
                'Amount Ordered'[Fiscal Week] > 9,
                'Sales Leads'[Lead Type] <> "Nationwide Buy Group"
            ),
            'Amount Ordered'[Fiscal Week], [DateOfFirstBuy]
        )
    )
RETURN
    COUNTROWS ( NewAccts )

If this doesn't work, can you show the relationship between 'Sales Leads' and 'Amount Ordered'? A sample file would be helpful. Thanks.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data