Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
karleek
Frequent Visitor

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

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 

 

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

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.

 

View solution in original post

3 REPLIES 3
DataNinja777
Super User
Super User

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.

 

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. 

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 

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.