Forum Discussion
Dax Logic
Hi Team,
I have table Name Attendees, Columns Attendee Date, and AttendeeID.
Created a Measure, Distinct Count of attendees.
Under Slicer Between Attendees Date Has taken, now the requirement is, I need to calculate the Returning and New Customers Count KPI
Eg : Attendee Date 1st Jan to 31st Jan
Count Attendees: 50
Returning: Need to check one year from Selected Time Period and above 5o Count is there anyone has attended the Session. if yes that count has to come under Returning KPI
so please help to create Dax on this Requirement
Hi MohammedGouse,
So you mean want to two step calculation to get the record count that both include current month and previous month?
If that is the case, you can add a variable and replace the raw formula count calculation with VALUES function to extract and store the id list. Then you can extract the previous month id list and use INTERSECT function to compare with above list to get the 'return' count.
INTERSECT function (DAX) - DAX | Microsoft Learn
Regards,
Xiaoxin Sheng
5 Replies
- Greg_Deckler
Community Champion
MohammedGouse Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Attaching the PBIX file for Refrence
Above Mentioned the image the Date range 1st Jan to 31st Jan Count of attendees were = 31
so the Requirement is need to create One more KPI Returning if any of this 31 attendees are attended before Jan 1st 2022 then that Count has to come under Returning Attendee
i am Unable to Attach Pbix Contten ading under Table
- olgad
Resident Rockstar
Hi, this is how you calculate generally the number of returning:
Returning Attendees=
CALCULATE( DISTINCTCOUNT('your_table_name'[attendee_id]),
FILTER( ALL('your_table_name'),
CALCULATE( COUNTROWS('your_table_name'),
FILTER( ALL('your_table_name'), 'your_table_name'[attendee_id] = EARLIER('your_table_name'[attendee_id]) ) ) > 1 ) )Hi Olgad,
Thanks for the Query but actually The Requirement
In Date Slicer i have Selected the Time Period 01/01/2022 to 01/31/2022 and the Count is 31 List of Employees were Snip Added
So Before that date we have 2021 Data As well
Now if select the Date Slicer as above mentioned Date Jan1st to 31st Jan Attedees are 31 , I need to Write a Dax Query any on of this 31 were attended Previous Month
if they have attended those count has comes under Returning. I have Adde the Image Please Review Once
As per Data It Should come under returning as 14 Count .Hi MohammedGouse,
So you mean want to two step calculation to get the record count that both include current month and previous month?
If that is the case, you can add a variable and replace the raw formula count calculation with VALUES function to extract and store the id list. Then you can extract the previous month id list and use INTERSECT function to compare with above list to get the 'return' count.
INTERSECT function (DAX) - DAX | Microsoft Learn
Regards,
Xiaoxin Sheng
