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.
Below is my data
ID DATE (DD/MM/YYYY)
4 01/04/2017
2 01/01/2017
1 01/09/2017
1 01/08/2017
1 1/1/2018
3 31/01/2019
4 01/08/2018
ACTIVE CUSTOMER:
If the customer having the transaction in between min and max dates in the slicer and also having the
transaction in the past less than 6 months starting from the min date in the slicer.
Example :
If I selected 01/01/2018 to 01/01/2019 In that period the customer have the transaction and also have the transaction in between 01/07/2017 to 01/01/2018 then consider as the Active customer
Inactive CUSTOMER
If the customer not having the transaction in between min and max dates in the slicer and also having the transaction in past + 6 months starting from the min date in slicer then consider as Inactive customer
Example:
If I selected 01/01/2018 to 01/01/2019 In that period the customer have the transaction and also have the transactions not in between 01/07/2017 to 01/01/2018 then consider as the Inactive customer
Reactive CUSTOMER:
If the customer having the transaction in between min and max dates in the slicer and also having the transaction in past + 6 months starting from the min date in slicer then consider as Reactive customer
Example:
If I selected 01/01/2018 to 01/01/2019 In that period the customer has the transaction and also have the transactions not in between 01/07/2017 to 01/01/2018 then consider as the Reactive customer
OUTPUT (If i selected date from 1/1/2018 to 1/1/2019 in slicer )
count of Active customers : 1(ID ="1")
count of Inactive CUSTOMER : 1(ID="2")
count of Reactive CUSTOMER : 1(ID="4")
ID DATE RELATION
1 01/01/2018 Active
2 01/01/2017 Inactive
4 01/08/2018 Reactive
Below is my Actual DATa
Solved! Go to Solution.
Hi,
I add some new data to the original test table:
I define this new customer status as 'New'.
Please try this measure:
Status =
VAR MinDate =
MIN ( 'Date Slicer'[Date] )
VAR MaxDate =
MAX ( 'Date Slicer'[Date] )
VAR InPast6MonthsDate =
IF (
MONTH ( MinDate ) <= 6,
DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ) + 6, DAY ( MinDate ) ),
DATE ( YEAR ( MinDate ), MONTH ( MinDate ) - 6, DAY ( MinDate ) )
)
RETURN
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[TransactionDate] <= MaxDate
&& 'Table'[TransactionDate] >= MinDate
)
) <> 0,
SWITCH (
TRUE,
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[TransactionDate] > InPast6MonthsDate
&& 'Table'[TransactionDate] < MinDate
)
) <> 0, "Active",
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
) <> 0, "Reactive",
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[TransactionDate] < MinDate )
) = 0, "New"
),
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
) <> 0,
"Inactive"
)
)
And change the original status table to this:
The result shows:
Here is my changed pbix file:
Best Regards,
Giotto Zhi
Hi,
Accotding to your description, i create a table to test:
Then create a date slicer table:
Date Slicer = CALENDAR(MIN('Table'[TransactionDate]),MAX('Table'[TransactionDate]))
Create a measure to show each customer's status:
Measure =
VAR MinDate =
MIN ( 'Date Slicer'[Date] )
VAR MaxDate =
MAX ( 'Date Slicer'[Date] )
VAR InPast6MonthsDate =
IF (
MONTH ( MinDate ) <= 6,
DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ) + 6, DAY ( MinDate ) ),
DATE ( YEAR ( MinDate ), MONTH ( MinDate ) - 6, DAY ( MinDate ) )
)
RETURN
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[TransactionDate] <= MaxDate
&& 'Table'[TransactionDate] >= MinDate
)
) <> 0,
SWITCH (
TRUE,
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[TransactionDate] > InPast6MonthsDate
&& 'Table'[TransactionDate] < MinDate
)
) <> 0, "Active",
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
) <> 0, "Reactive"
),
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
) <> 0,
"Inactive"
)
)
Then create a status table by 'Enter Data':
Create a measure to count rows:
Measure = COUNTROWS(FILTER(SUMMARIZE('Table','Table'[CustomerID],"Status",[Status]),[Status] in FILTERS('Status'[Status])))
Choose the data from date slicer table as a slicer visual, when select the date duration, it shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto Zhi
@v-gizhi-msft can we add New Customers also with the existing formula?
New Customer means, Customers, having the transaction only in that particular date period (Slicer Min and MAX date ).
Thank&Regards
Mani deep.
@v-gizhi-msft Your Measure is not working, Because I have 4 Trancations tables those tables have relationships with Date slicer , below is my relationship
Slicer Table[Date], Table A[Date] Active Relationship many to one
Slicer Table[Date], Table B[Date] Active Relationship many to one
Slicer Table[Date], Table C[Date] Active Relationship many to one
Slicer Table[Date], Table D[Date] Active Relationship many to one
Hi,
Please try to combine these four tables by UNION and SELECTCOLUMNS:
Union Table =
UNION (
SELECTCOLUMNS (
TableA,
"CustomerID", TableA[CustomerID],
"TranscationID", TableA[TranscationID]
),
SELECTCOLUMNS (
TableB,
"CustomerID", TableB[CustomerID],
"TranscationID", TableB[TranscationID]
),
SELECTCOLUMNS (
TableC,
"CustomerID", TableC[CustomerID],
"TranscationID", TableC[TranscationID]
),
SELECTCOLUMNS (
TableD,
"CustomerID", TableD[CustomerID],
"TranscationID", TableD[TranscationID]
)
)
Best Regards,
Giotto Zhi
Measure = VAR MinDate = MIN ( 'Date Slicer'[Date] ) VAR MaxDate = MAX ( 'Date Slicer'[Date] ) VAR InPast6MonthsDate = IF ( MONTH ( MinDate ) <= 6, DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ) + 6, DAY ( MinDate ) ), DATE ( YEAR ( MinDate ), MONTH ( MinDate ) - 6, DAY ( MinDate ) ) ) RETURN IF ( CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[TransactionDate] <= MaxDate && 'Table'[TransactionDate] >= MinDate ) ) <> 0, SWITCH ( TRUE, CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[TransactionDate] > InPast6MonthsDate && 'Table'[TransactionDate] < MinDate ) ) <> 0, "Active", CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate ) ) <> 0, "Reactive" ), IF ( CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate ) ) <> 0, "Inactive" ) )
For the above formula can we add the new customers also
new customers mean the customers who have the transactions only in that particular period of time (Slicer date)
Hi,
I add some new data to the original test table:
I define this new customer status as 'New'.
Please try this measure:
Status =
VAR MinDate =
MIN ( 'Date Slicer'[Date] )
VAR MaxDate =
MAX ( 'Date Slicer'[Date] )
VAR InPast6MonthsDate =
IF (
MONTH ( MinDate ) <= 6,
DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ) + 6, DAY ( MinDate ) ),
DATE ( YEAR ( MinDate ), MONTH ( MinDate ) - 6, DAY ( MinDate ) )
)
RETURN
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[TransactionDate] <= MaxDate
&& 'Table'[TransactionDate] >= MinDate
)
) <> 0,
SWITCH (
TRUE,
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
'Table',
'Table'[TransactionDate] > InPast6MonthsDate
&& 'Table'[TransactionDate] < MinDate
)
) <> 0, "Active",
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
) <> 0, "Reactive",
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[TransactionDate] < MinDate )
) = 0, "New"
),
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER ( 'Table', 'Table'[TransactionDate] < InPast6MonthsDate )
) <> 0,
"Inactive"
)
)
And change the original status table to this:
The result shows:
Here is my changed pbix file:
Best Regards,
Giotto Zhi
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 |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |