Forum Discussion
Helper IIIcount orders after 60 days between dates
Hello,
I need to count the number of orders by email address and Order Date based on the criteria:
1. Only count the order if the previous Order by email address is more then 60 days old.
As an example we don't count the second order by Mike because the order date is within 60 days.
We count the second order by Dave because more then 60 days have elapsed.
4 Replies
- lbendlin
Super User
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
mikebiHello,
This is the sample data.
[email protected] 1/1/2023 Yes [email protected] 2/1/2023 No [email protected] 1/1/2023 Count [email protected] 4/1/2023 Yes more then 60 days
- Ahmedx
pls try this
Count = VAR _t1 = [Email] VAR _tbl = FILTER ( ALL ( 'Table' ), 'Table'[Email] = _t1 ) VAR _minDate = MINX ( _tbl, [OrderDate] ) RETURN SWITCH ( TRUE (), [OrderDate] = _minDate || DATEDIFF ( _minDate, [OrderDate], DAY ) > 60, 1, 0 )- mikebi
Hello,
This seems to work:
Count =VAR _t1 =SelectedValue(Rolling[Email])VAR _t2 = SelectedValue(Rolling[OrderDate])VAR _tbl =FILTER ( ALL ( 'Rolling' ), Rolling[Email] = _t1 && Rolling[OrderDate] < _t2 )VAR _maxDate =maxx ( _tbl, [OrderDate] )RETURNSWITCH (TRUE (),IsBlank(_maxDate)|| DATEDIFF ( _maxDate, SelectedValue(Rolling[OrderDate]), DAY ) > 60, 1,0)
