Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have been struggling for a couple of days with the following case:
This is my data:
PART NO | ORDER NO | TRANSACTION DATE | QTY |
ABC | 1001 | 4/11/2020 | 200 |
ABC | 1001 | 4/11/2020 | 200 |
ABC | 1001 | 5/11/2020 | 200 |
ABC | 1004 | 6/11/2020 | 200 |
ABC | 1004 | 9/11/2020 | 200 |
CDE | 1009 | 7/11/2020 | 300 |
CDE | 1009 | 8/11/2020 | 300 |
My intention is to calculate how many different orders that were produced. In theory this would be a distinct count, but in practice, as with case order no = 1004 when the order was paused and continued after order 1009 was done. The accurate result would be to calculate that these were two separate orders.
The correct result (order count) would be:
1001 = 1
1004 = 2
1009 = 1
--> 4 instead of a distinct count that would result in 3.
Any takes on a solution for this? I am thinking that based on transaction date one could say that if the order no is the same and date since last transaction for this product is within 1 day, then it is one, else it counts how many different days it was produced on.
Thanks in advance for any help!
Solved! Go to Solution.
Hi @Anonymous ,
Based on your description, I created an index column in the query editor as a filter condition to create a calculated column.
diff =
VAR _diff =
CALCULATE (
FIRSTNONBLANK ( 'Table (2)'[TRANSACTION DATE], 1 ),
FILTER ( 'Table (2)', EARLIER ( 'Table (2)'[Index] ) < 'Table (2)'[Index] )
)
VAR day_ =
DATEDIFF ( _diff, 'Table (2)'[TRANSACTION DATE], DAY )
RETURN
IF(ISBLANK(day_),0,day_)
Then create a measure like this:
Measure =
VAR diff_1 =
CALCULATE ( DISTINCTCOUNT ( 'Table (2)'[ORDER NO] ), 'Table (2)'[diff] <= 0 )
VAR diff_2 =
CALCULATE ( COUNT ( 'Table (2)'[ORDER NO] ), 'Table (2)'[diff] > 0 )
RETURN
diff_2 + diff_1
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the input. I think that solution will be better than the one I derived from @amitchandak's reply.
The solution I have now is really slow, so I might check out your solution later when I have time the time to improve it. This is the solution:
var _prod = Table[PART NO]
var _datesapplied = Table[DATE_TIME]
var _lastday =
maxx(
FILTER(Table,
Table[ProductKey] = _prod &&
Table[DATE_TIME] < _datesapplied),
Table[DATE])
var _diff = DATEDIFF(_lastday, _datesapplied,DAY)
return
SWITCH(TRUE(),
_diff = 0,0,
1)
I then created a measure that calcuated the distinct ORDER NO where this column = 1
CALCULATE(
DISTINCTCOUNT('Table'[SOURCE_REFKEY]),
Table[Shop Order Count] = 1)
Thanks for the input!
Hi @Anonymous ,
Based on your description, I created an index column in the query editor as a filter condition to create a calculated column.
diff =
VAR _diff =
CALCULATE (
FIRSTNONBLANK ( 'Table (2)'[TRANSACTION DATE], 1 ),
FILTER ( 'Table (2)', EARLIER ( 'Table (2)'[Index] ) < 'Table (2)'[Index] )
)
VAR day_ =
DATEDIFF ( _diff, 'Table (2)'[TRANSACTION DATE], DAY )
RETURN
IF(ISBLANK(day_),0,day_)
Then create a measure like this:
Measure =
VAR diff_1 =
CALCULATE ( DISTINCTCOUNT ( 'Table (2)'[ORDER NO] ), 'Table (2)'[diff] <= 0 )
VAR diff_2 =
CALCULATE ( COUNT ( 'Table (2)'[ORDER NO] ), 'Table (2)'[diff] > 0 )
RETURN
diff_2 + diff_1
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the input. I think that solution will be better than the one I derived from @amitchandak's reply.
The solution I have now is really slow, so I might check out your solution later when I have time the time to improve it. This is the solution:
var _prod = Table[PART NO]
var _datesapplied = Table[DATE_TIME]
var _lastday =
maxx(
FILTER(Table,
Table[ProductKey] = _prod &&
Table[DATE_TIME] < _datesapplied),
Table[DATE])
var _diff = DATEDIFF(_lastday, _datesapplied,DAY)
return
SWITCH(TRUE(),
_diff = 0,0,
1)
I then created a measure that calcuated the distinct ORDER NO where this column = 1
CALCULATE(
DISTINCTCOUNT('Table'[SOURCE_REFKEY]),
Table[Shop Order Count] = 1)
Thanks for the input!
Another way of thinking about would be to count the distinct DAYS with the exception of if the same order is produced for several days. Maybe that would help to think it the other way around?
@Anonymous , a column like this will give you 0 for for date diff greater than 1. See if this can help
Column =
var _1 = datediff(maxx(FILTER('Table','Table'[ORDER ]= EARLIER('Table'[ORDER]) && 'Table'[Date] < EARLIER('Table'[Date])),'Table'[Date]) , 'Table'[Date],day)
return if(_1 <=1,1,0)
Thanks for the reply. Did not work though.
I receive 1 for every case. I guess it would need to iterate based on the product column? Now order = earlier(order) would basically mean (if I've understood it correctly) that we take the date of the order before that?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |