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.
I'm trying to calculate the number of transactions each customer had in a given 18 month period of time. Here is what I have so far, but it is not calculating the correct number of transactions. The formula below is a calculated column.
Lost Count # Trans2 = CALCULATE(COUNTX('Customer Base','Customer Base'[Transaction ID]),DATESBETWEEN('Customer Base'[Transaction Date],'Customer Base'[Start of Month of Transaction DAte],[Lost Month]))
I have 1 table that contains all transactions for all customers. It includes:Customer ID, transaction date, start of month of transaction date, lost month
For example it looks like this:
Customer ID Transaction ID Start of Month of Transaction Lost Month Count of Trans(formula above)
ABC 1 1/1/15 3/1/16 3
ABC 2 4/1/15 7/1/16 2
DEF 3 1/1/15 3/1/16 1
ABC 4 7/1/15 10/1/16 1
The numbers in bold in the Count of Trans column are what I want to see, but currently the formula is giving me a 1 for each, because it is not grouping the customer ID's together and counting them collectively for that period of time.
The idea is to then be able to say, if a customer has a 1, that means the customer has not ordered in that 18 month time period
Solved! Go to Solution.
Hi @wmeyer
try this
Column 2 = IF ( [ Lost Month ] <> BLANK (), CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( ALLEXCEPT ( Table1, Table1[Customer ID] ), ( Table1[Start of Month of Transaction] >= EARLIER ( Table1[Start of Month of Transaction] ) ) && ( Table1[Start of Month of Transaction] ) <= EARLIER ( Table1[ Lost Month ] ) ) ), CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( ALLEXCEPT ( Table1, Table1[Customer ID] ), Table1[Start of Month of Transaction] >= EARLIER ( Table1[Start of Month of Transaction] ) ) ) )
explanation:
Column 2 =
IF (
[ Lost Month ] <> BLANK (), //judge if the 'Lost Month' column is blank//
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER (
ALLEXCEPT ( Table1, Table1[Customer ID] ), //returen a table with all filters removed except for the filter on the 'Customer ID' column//
(
Table1[Start of Month of Transaction]
>= EARLIER ( Table1[Start of Month of Transaction] ) // filter the context which date is larger than or equal
to the current row context //
)
&& ( Table1[Start of Month of Transaction] ) <= EARLIER ( Table1[ Lost Month ] )
) //meet two conditions above at the same time//
),
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER (
ALLEXCEPT ( Table1, Table1[Customer ID] ),
Table1[Start of Month of Transaction]
>= EARLIER ( Table1[Start of Month of Transaction] )
)
)
)
Best regards
Maggie
Hi @wmeyer
Try this formula instead
Column 2 = CALCULATE ( COUNTROWS ( 'Customer Base' ), FILTER ( ALLEXCEPT ( 'Customer Base', 'Customer Base'[Customer ID] ), 'Customer Base'[Start of Month of Transaction] >= EARLIER ( 'Customer Base'[Start of Month of Transaction] ) ) )
Best regards
Maggie
Maggie, This appears to have worked, partially. I have some rows where it is not calculating the correct totals.
I'm confused how this works with out using the Lost Month Date column.
Could you help me understand what each function in the formula is doing? I'd like to get a better understanding so i can use these functions and apply them to other things done the road, once we get this figured out.
Hi @wmeyer
try this
Column 2 = IF ( [ Lost Month ] <> BLANK (), CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( ALLEXCEPT ( Table1, Table1[Customer ID] ), ( Table1[Start of Month of Transaction] >= EARLIER ( Table1[Start of Month of Transaction] ) ) && ( Table1[Start of Month of Transaction] ) <= EARLIER ( Table1[ Lost Month ] ) ) ), CALCULATE ( COUNTROWS ( 'Table1' ), FILTER ( ALLEXCEPT ( Table1, Table1[Customer ID] ), Table1[Start of Month of Transaction] >= EARLIER ( Table1[Start of Month of Transaction] ) ) ) )
explanation:
Column 2 =
IF (
[ Lost Month ] <> BLANK (), //judge if the 'Lost Month' column is blank//
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER (
ALLEXCEPT ( Table1, Table1[Customer ID] ), //returen a table with all filters removed except for the filter on the 'Customer ID' column//
(
Table1[Start of Month of Transaction]
>= EARLIER ( Table1[Start of Month of Transaction] ) // filter the context which date is larger than or equal
to the current row context //
)
&& ( Table1[Start of Month of Transaction] ) <= EARLIER ( Table1[ Lost Month ] )
) //meet two conditions above at the same time//
),
CALCULATE (
COUNTROWS ( 'Table1' ),
FILTER (
ALLEXCEPT ( Table1, Table1[Customer ID] ),
Table1[Start of Month of Transaction]
>= EARLIER ( Table1[Start of Month of Transaction] )
)
)
)
Best regards
Maggie
Maggie, This is now calculating correctly for all rows! Thank you so much! You saved me countless hours trying to figure this out 🙂
How did you become so good at formulas? I'm looking for a mentor/training or something that can help me become better at them.
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |