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

[karleek]

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 

[DataNinja777 · Accepted solution]

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.

[karleek]

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. 

[karleek]

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