Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Trying to think how best to go about a measure my boss has asked for.
He wants data on "active" accounts, meaning by customer how many months have the traded.
E.g. Customer ABC Ltd, traded in Jan, Feb, March - so 3 months of trade
Customer 123 PLC, traded Jan, March, April, Nov, Dec - so 5 months of trade
For this to be relative, I think it will need to be based on the prior 12 months rolling rather than a calendar year.
I could quite easily visualize this, but the aim is more to identify customers by "activity" (i.e. months traded), then put them in buckets 0-3 Low, 4-6 Medium, 7-12 High (based on last 12 moths)
My data is on an invoice table like this (each row is the invoice date)
Customer | Part | Invoice Date | ||
ABC LTD | XXXXXX | 01/01/2022 | ||
ABC LTD | YYYYYY | 20/02/2022 | ||
ABC LTD | LLLLLL | 03/03/2022 | ||
123 PLC | XXXXXX | 21/01/2022 | ||
123 PLC | XXXXXX | 03/03/2022 | ||
123 PLC | YYYYYY | 14/04/2022 | ||
123 PLC | YYYYYY | 10/11/2022 | ||
123 PLC | YYYYYY | 25/12/2022 |
Thanks for this.
It functions, but it doesnt quite do what I wanted it to (my fault for not being clear).
I dont want the count of activity on each month, rather I want to know if a month has had any activity at all (i.e. an active month, if its 1 or 1000 transactiosn), then the how many months have been active.
Example, results I want to see is for Jan there was activity, sames for Feb, so 1 for Jan, 1 for Feb, means 2 months of activity (in the last 12 months).
Hi @mattrixdesign2 ,
I suggest you to create a Calendar table to help Calculation.
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2022, 01, 01 ), DATE ( 2023, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "MMMM" ),
"MonthSort", MONTH ( [Date] )
)
Measure:
Buckets =
VAR _RANGEEND =
MAX ( 'Calendar'[Date] )
VAR _RANGESTART =
EOMONTH ( _RANGEEND, -12 ) + 1
VAR _Filter12Period =
FILTER (
'Table',
'Table'[Invoice Date] >= _RANGESTART
&& 'Table'[Invoice Date] <= _RANGEEND
)
VAR _Count =
COUNTAX ( _Filter12Period, [Invoice Date] )
RETURN
SWITCH (
TRUE (),
_Count <= 3, "0-3 Low",
_Count <= 6, "4-6 Medium",
_Count <= 12, "7-12 High"
)
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.