topic Re: How to count distinct after period in DAX Commands and Tips

How to count distinct after period

Anonymous — Thu, 19 Nov 2020 08:12:03 GMT

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.

Re: How to count distinct after period

wdx223_Daniel — Thu, 19 Nov 2020 08:27:01 GMT

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)

Re: How to count distinct after period

amitchandak — Thu, 19 Nov 2020 08:33:43 GMT

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))

refer https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: How to count distinct after period

Anonymous — Mon, 23 Nov 2020 08:31:44 GMT

[# 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

Re: How to count distinct after period

Anonymous — Mon, 23 Nov 2020 08:35:48 GMT

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