New distinct customer Count on basis of condition

[ShubhamWarang]

Hi everyone actually i was trying to find Distinct customer count in dax i have one table is test in that i want to find distinct count of customer whose last year sales less than zero and current year sales is greater than zero for eg.if customer 4 had not done sales in last year and in current year he had done sales then count as one else zero so i want to make it dynamic so if select april and year 2023 then it should calculate presvious year start of the month means april 2022 to current selectded month minus one means till march 2022 cumulative sales. if i select june 2023 then it should caluculate cumulative sales from april 2022 to may 2023

TEST :-

Customer	Month	Year	Region	Sales
1	April	2022	east	1200
2	May	2022	west	1400
3	Feb	2022	south	1400
2	April	2023	west	1300
4	June	2023	north	1200
5	august	2023	east	5000

[123abc]

To achieve this in DAX (Data Analysis Expressions), you can create a calculated column or measure in your Power BI or other DAX-supported tool. Here's an example measure that you can use to calculate the distinct customer count based on your conditions:

DistinctCustomerCount =
CALCULATE(
DISTINCTCOUNT('TEST'[Customer]),
FILTER(
VALUES('TEST'[Customer]),
CALCULATE(
SUM('TEST'[Sales]),
FILTER(
'TEST',
'TEST'[Year] = YEAR(MAX('TEST'[Year])) - 1 &&
'TEST'[Month] <= MAX('TEST'[Month])
)
) < 0 &&
CALCULATE(
SUM('TEST'[Sales]),
FILTER(
'TEST',
'TEST'[Year] = YEAR(MAX('TEST'[Year])) &&
'TEST'[Month] <= MAX('TEST'[Month])
)
) > 0
)
)

This measure first filters the customers based on your conditions (last year sales < 0 and current year sales > 0). It then calculates the distinct count of customers meeting these conditions.

You can use this measure in your report, and it will dynamically adjust based on the selected month and year.

Remember to replace 'TEST' with the actual name of your table if it's different. Also, ensure that the 'Month' column is in a format that allows proper comparison (e.g., as a number or a properly sorted text format).

Feel free to adapt the measure based on your specific data model and requirements.

[ShubhamWarang · Accepted solution]

I tried it, but it doesn't work; it returns a blank value. So I generated one date column with the assistance of the month and year columns, and then I established one table, Dates, with the aid of which I created a new measure.
 previous sales= 

var z=max(CP_Dates[Date])

return

CALCULATE(sum(Test[Gross]),FILTER(ALLSELECTED(CP_Dates[Date]),CP_Dates[Date]>=DATE(2022,4,1) &&

CP_Dates[Date]<DATE(YEAR(z),MONTH(z)-1,DAy(z))))

current sales=

VAR K=SELECTEDVALUE(Test[Division])

var CM=CALCULATE(SUM(Test[Gross]),FILTER(CP_Dates,CP_Dates[Month]=SELECTEDVALUE(CP_Dates[Month]) && CP_Dates[Year]=SELECTEDVALUE(CP_Dates[Year])))

var UniqueCustomers = COUNTROWS ( FILTER ( VALUES ( Test[Kunnr_Pl] ), previous sales=BLANK() && CM>0 ) )

RETURN  
UniqueCustomers