topic Re: Measure to Determine if Customer is Active during selected date range AND sameperiodlastyear range in DAX Commands and Tips

Measure to Determine if Customer is Active during selected date range AND sameperiodlastyear range

karleek — Fri, 01 Nov 2024 19:59:06 GMT

I am needing to create a measure that achieve the following:

1. determine if customer (unique id) has orders during selected date range on slicer

2. determine if customer (unqiue id) has order during the sameperiodlastyear of the date range on slicer

3. If they answer to both of the above is YES, then calculate the total number of orders for date range in slicer

Any help on this would be much appreciated.

@Ahmedx @amitchandak @Ashish_Mathur @danextian @Fowmy @Greg_Deckler @parry2k @talespin @tamerj1 AlexisOlson Jihwan_Kim lbendlin Anonymous

Re: Measure to Determine if Customer is Active during selected date range AND sameperiodlastyear range

DataNinja777 — Sat, 02 Nov 2024 06:15:31 GMT

Hi karleek ,

Your requirement sounds relatively straightforward, and you can achieve the desired output by writing a DAX formula like the one below

TotalOrdersIfInBothPeriods = VAR CustomersInSelectedRange = CALCULATE( DISTINCTCOUNT('Sales transaction'[Customer code]), 'Sales transaction'[Posting Date] >= MIN('Calendar'[Date]) && 'Sales transaction'[Posting Date] <= MAX('Calendar'[Date]) ) VAR CustomersInSamePeriodLastYear = CALCULATE( DISTINCTCOUNT('Sales transaction'[Customer code]), SAMEPERIODLASTYEAR('Calendar'[Date]) ) VAR OrdersInSelectedRange = CALCULATE( countrows('Sales transaction'), 'Sales transaction'[Posting Date] >= MIN('Calendar'[Date]) && 'Sales transaction'[Posting Date] <= MAX('Calendar'[Date]) ) RETURN IF( CustomersInSelectedRange > 0 && CustomersInSamePeriodLastYear > 0, OrdersInSelectedRange, 0 )

I have taken order to be equivalent to sales, and used the dummy sales data. Please find attached an example pbix file.

Re: Measure to Determine if Customer is Active during selected date range AND sameperiodlastyear range

karleek — Mon, 18 Nov 2024 16:20:44 GMT

This worked beautifully thank you.

Now I need to create another measure that takes it a step further.

If the selected range is 1/1/2024 - 10/31/2024 and the customer has active orderw within that time range and they also have orders within PART of the sameperiodlast year, orders only within 6/1/2023 - 10/31/2023, I want my total order measure to ONLY add in the orders for 2024 for 6/1/2024 - 10/31/2024 so that I can compared YoY order volume change for the same period. Does that make sense?

@Ahmedx @amitchandak @Ashish_Mathur @danextian @Fowmy @Greg_Deckler @parry2k @talespin @tamerj1 @AlexisOlson @Jihwan_Kim @lbendlin @v-cgao-msft

Re: Measure to Determine if Customer is Active during selected date range AND sameperiodlastyear range

karleek — Tue, 21 Jan 2025 22:55:57 GMT

I know it has been a while, but recirculating this data reporting project. While the above measure works, when I use it within a matrix the subtotal and there totals are inaccurate. I understand that is because the subtotals and totals are looking at the full data range and ignoring the filters within the rest of the measure, but how do I fix that? I really need the measure to provide an accurate subtotal and total within a matrix.