Dax Measure to determine Active versus Inactive Clients for like-for-like comparisons

[karleek]

I have a measure to create a table to determine which clients are active based on if they have orders within a specific month & year. Here is the measure creating this table

All Customer =

var AllCustomer =

CROSSJOIN(

    FILTER(

        ALLNOBLANKROW('Date'[Year]), 'Date'[Year]),

        ALLNOBLANKROW('Date'[Month], 'Date'[Month]),

        ALLNOBLANKROW('Customer Table'[Final Business ID]))

        var LiveCustomer =

        SUMMARIZE(

        'New Exchange Info','Date'[Year],'Date'[Month],'Customer Table'[Final Business ID])

    var Result =

    UNION(ADDCOLUMNS(LiveCustomer, "Status", "Live"),

    ADDCOLUMNS(EXCEPT(AllCustomer, LiveCustomer), "Status", "InActive"))

    Return

    Result

This measure below is ALMOST working properly, but there are couple issues as following:

1. It is calling all clients Inactive for Aug - December since those month haven't occured yet and therefore there are no orders for those months

2. My like-for-like measures is expecting a client to be Active for the entire year in order to show in my comparison charts when really I just want it to look at if they were active for the "previous same period" 

An example of the like-for-like measure calculating all orders is as follows: 

Same Customer Orders =

var LiveCustomer =

CALCULATETABLE(

    FILTER(

        ALLSELECTED('Customer Table'[Final Business ID),

        CALCULATE(

            SELECTEDVALUE('All Customer'[Status])

    ) = "Live"),

            ALLSELECTED('Date'))

            var FilterCustomer =

            TREATAS(LiveCustomer, 'Customer Table'[Final Business ID])

            var Result =

            CALCULATE(

                [Total Orders],

                KEEPFILTERS(filterCustomer))

                Return

                Result

Any help in tweaking these measures. to accomplish what I need would be much appreciated. Thanks! @Ahmedx @AlexisOlson @amitchandak @danextian 

[Anonymous]

Hi, @karleek 

Have you solved your problem? If not, is it possible to provide some of the example data and the output you expect.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[karleek]

@AlexisOlson  thanks for the reply, no AI didn't write it, I found the measure examples on this article https://www.daxpatterns.com/like-for-like-comparison/ I am new to Power BI and just trying to learn it based on the desired reporting outputs needed, so very much a trial and error experience over here.

I am testing about what you provided above, but what is supposed to go in the [@monthslive]?

[AlexisOlson]

It should be MonthsSelected (I rewrote the measures a couple of times and this was a vestige of an earlier version). I will correct it.

[AlexisOlson]

I'm not fully understanding how you intend to use the measure. Same customer orders as what?

BTW, did an AI write these measures? They seem overly convoluted to me. I think you could combine both All Customer and Same Customer Orders into a single measure like this:

Live Customer Orders =

VAR EndDate = EOMONTH ( TODAY (), -1 )

VAR MonthsSelected =
    CALCULATE (
        COUNTROWS ( SUMMARIZE ( 'Date', 'Date'[Year], 'Date'[Month] ) ),
        'Date'[Date] <= EndDate
    )

VAR CustomerMonths =
    CALCULATETABLE (
        SUMMARIZE ( 'New Exchange Info', 'Customer'[ID], 'Date'[Year], 'Date'[Month] ),
        'Date'[Date] <= EndDate
    )

VAR LiveCustomers =
    FILTER (
        DISTINCT ( 'Customer'[ID] ),
        COUNTROWS (
            FILTER ( CustomerMonths, 'Customer'[ID] = EARLIER ( 'Customer'[ID] ) )
        ) = MonthsSelected
    )

VAR Result = CALCULATE ( [Total Orders], KEEPFILTERS ( LiveCustomers ) )

RETURN
    Result