Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ShubhamWarang
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 :-

CustomerMonthYearRegionSales

1

April2022east1200
2May2022west1400
3Feb2022south1400
2April2023west1300
4June2023north1200
5august2023east5000
1 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




View solution in original post

2 REPLIES 2
123abc
Resident Rockstar
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.

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




Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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