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 Have a table in which customer information is saved.
CustomerID | CustomerName | JoinedDate |
1 | Alexender | 2017-08-10 |
2 | David | 2018-02-14 |
3 | Didier | 2016-01-14 |
4 | Laurent | 2019-03-02 |
5 | Giulia | 2017-01-01 |
My Boss has asked me to create a drill down report where in he wants all active customer count.
1st condition. suppose a customer is joined in year 2017 then for every year after 2017 the count should be as 1. 2nd condition. suppose a customer is joined in month 2018-Feb then for every month after 2018-Feb the count should be as 1.
same goes with "Day" drilldown as well.
I created a measure where in I have written below code but that didn't work. I have also created a calculated column for year and created a non active relationship between both tables.
CALCULATE(
DISTINCTCOUNT('Customer Status'[CustomerID]),
USERELATIONSHIP('Time'[Year],'Customer Status'[Customer Status Year])
)
This is working for year but for month it is showing same count for all month.
Requesting your help.
Here's another formula that should return the same as my previous one that uses SUMX
[# Active Customers] =
// It'll return the number
// of customers active
// according to your condition
// regardless of the time slice
// selected. Time should be
// connected on Date(!) to
// 'Customer Status'[JoinedDate]
// via a 1-to-many (one-way filtering).
var __minDate = min( Time[Date] )
var __output =
CALCULATE(
COUNTROWS( 'Customer Status'[CustomerID] ),
KEEPFILTERS(
'Customer Status'[JoinedDate] < __minDate
)
)
return
// This time, if you want to return BLANKS as 0's,
// just use __output + 0 instead of just __output.
__output
[# Active Customers] =
// It'll return the number
// of customers active
// according to your condition
// regardless of the time slice
// selected. Time should be
// connected on Date(!) to
// 'Customer Status'[JoinedDate]
// via a 1-to-many (one-way filtering).
var __minDate = min( Time[Date] )
var __output =
SUMX(
DISTINCT( 'Customer Status'[CustomerId] ),
// If the selected period in Time
// has all dates >= JoinedDate, then
// return 1. Otherwise 0.
CALCULATE(
VAR __joinedDate =
SELECTEDVALUE( 'Customer Status'[JoinedDate] )
return
__minDate > __joinedDate
)
)
return
// If you want to return 0's as BLANKS,
// just use IF( __output > 0, __output )
// instead of just returning __output.
__output
@Anonymous , Create a date table, Create only inactive join with joining date
Current Customer = CALCULATE(COUNTx(FILTER('Customer Status','Customer Status'[Joined Date]<=max('Date'[Date]) ),('Customer Status'[CustomerID])))
Current Customer = CALCULATE(distinctcount('Customer Status'[CustomerID]), FILTER('Customer Status','Customer Status'[Joined Date]<=max('Date'[Date]) ))
if join is active
Current Customer = CALCULATE(COUNTx(FILTER('Customer Status','Customer Status'[Joined Date]<=max('Date'[Date]) ),('Customer Status'[CustomerID])),CROSSFILTER(Employee[Join Date Date],'Date'[Date],None))
@Anonymous this might work if only the date table is filterling the visual
Distinct_Count:=VAR _MaxDate=MAX('Calendar'[Date]) RETURN CALCULATE(DISTINCTCOUNT(CustomerStatus[CustomerID]),'Calendar'[Date]<=_MaxDate)
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 |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |