Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone!
I have this table:
I want to create a measure that for each whs_id && outlet_id calculates the day difference between the current order to the previous order
for example:
whs_id = 59706
outlet_id = 60809
the day difference between order 1453423 paid_date - 2/28/2023
to order 1456751 order_created_date - 1/30/2023 is -29 days, so the measure should give me "-29"
Solved! Go to Solution.
Hi @sapirmarko ,
I suggest you to create a measure by below code.
Diff =
VAR _ADDRANK =
ADDCOLUMNS (
'Table',
"Rank",
RANKX (
FILTER (
'Table',
'Table'[whs_id] = EARLIER ( [whs_id] )
&& 'Table'[outlet_id] = EARLIER ( [outlet_id] )
),
'Table'[order_ceated_date],
,
DESC,
DENSE
)
)
VAR _ADDDIFF =
SUMMARIZE (
_ADDRANK,
[whs_id],
[outlet_id],
"Diff",
VAR _CURRENT =
MAXX (
FILTER (
_ADDRANK,
[whs_id] = EARLIER ( [whs_id] )
&& [outlet_id] = EARLIER ( [outlet_id] )
&& [Rank] = 1
),
[order_ceated_date]
)
VAR _PREVIOUS =
MAXX (
FILTER (
_ADDRANK,
[whs_id] = EARLIER ( [whs_id] )
&& [outlet_id] = EARLIER ( [outlet_id] )
&& [Rank] = 2
),
[paid_date]
)
RETURN
DATEDIFF ( _PREVIOUS, _CURRENT, DAY )
)
RETURN
SUMX ( _ADDDIFF, [Diff] )
My Sample:
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sapirmarko ,
I suggest you to create a measure by below code.
Diff =
VAR _ADDRANK =
ADDCOLUMNS (
'Table',
"Rank",
RANKX (
FILTER (
'Table',
'Table'[whs_id] = EARLIER ( [whs_id] )
&& 'Table'[outlet_id] = EARLIER ( [outlet_id] )
),
'Table'[order_ceated_date],
,
DESC,
DENSE
)
)
VAR _ADDDIFF =
SUMMARIZE (
_ADDRANK,
[whs_id],
[outlet_id],
"Diff",
VAR _CURRENT =
MAXX (
FILTER (
_ADDRANK,
[whs_id] = EARLIER ( [whs_id] )
&& [outlet_id] = EARLIER ( [outlet_id] )
&& [Rank] = 1
),
[order_ceated_date]
)
VAR _PREVIOUS =
MAXX (
FILTER (
_ADDRANK,
[whs_id] = EARLIER ( [whs_id] )
&& [outlet_id] = EARLIER ( [outlet_id] )
&& [Rank] = 2
),
[paid_date]
)
RETURN
DATEDIFF ( _PREVIOUS, _CURRENT, DAY )
)
RETURN
SUMX ( _ADDDIFF, [Diff] )
My Sample:
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks!!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |