topic Re: Dax Measure to determine Active versus Inactive Clients for like-for-like comparisons in DAX Commands and Tips

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

karleek — Fri, 02 Aug 2024 17:58:17 GMT

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

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

AlexisOlson — Fri, 02 Aug 2024 20:37:51 GMT

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

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

karleek — Fri, 02 Aug 2024 19:12:06 GMT

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]?

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

AlexisOlson — Fri, 02 Aug 2024 20:38:53 GMT

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.

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

Anonymous — Mon, 05 Aug 2024 06:30:09 GMT

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.