cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

New distinct customer Count on basis of condition

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
1 ACCEPTED SOLUTION
Frequent Visitor

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

2 REPLIES 2
Resident Rockstar

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.

Frequent Visitor

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

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors