The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
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
User | Count |
---|---|
12 | |
8 | |
6 | |
6 | |
6 |
User | Count |
---|---|
24 | |
14 | |
13 | |
9 | |
7 |