0

I have two simple tables. I need to able to determine who is 'new' as of a particular date (say January) and then count only those attributes. There's a 1:M relationship on name. I basically need to answer the following questions with the below data:

  1. What is the total number of FamilyMembers based on log-in for the month? (Done using custom measure)
  2. Out of the total of #1 - how many have logged in for the first time?
  3. Out of the total of #2 - how many were children? How many were adults?

Log In Table

ID Name Date
login1 Sam Jan
login2 Sam Jan
login3 Dave Jan
login4 Dave Jan
login5 Jack Jan
login6 Sam Jan
login7 James Feb
login8 James Feb
login9 James Feb
login10 Sam Feb
login11 Sam Feb
login12 Steve Feb

Contact Table

Name FamilyMembers Child Adult
Sam 3 1 2
James 2 1 1
Dave 4 2 2
Jack 1 0 1
Steve 6 1 5

Using this data, filtered on February we would see Steve never signed in prior to that date, so that makes him 'new'. James is also new.

My closest attempt is the custom 'Count of New Individuals' Measure


VAR currentUsers = VALUES('Log-Ins'[Name])
VAR currentDate = MIN('Log-Ins'[Date])

VAR pastUsers = CALCULATETABLE(VALUES('Log-Ins'[Name]), 
    ALL('Log-Ins'[Date].[Month],'Log-Ins'[Date].[MonthNo],'Log-Ins'[Date].[Year])
    , 'Log-Ins'[Date]<currentDate)

VAR newUsers = EXCEPT(currentUsers,pastUsers)

RETURN COUNTROWS(newUsers)

As you can see this gives me the count of new individuals but I want to count their attributes to say :: Out of the 11 total family members, 8 were new. Out of those 8, 6 were adults and 2 were children.

pbiImage

Insan3
  • 37
  • 7

1 Answers1

0

I may be getting lost in the translation, but I don't understand how exactly you want to display the information.

enter image description here

#ContactsWhoLoggedIN := 
CALCULATE(COUNTROWS(Contacts),FILTER(Contacts,CALCULATE(COUNTROWS(LogIN)>0)))

#NewCWhoLoggedIN := 
CALCULATE(COUNTROWS(Contacts),
FILTER(Contacts,
//LoggedIn in the Current Date Context
CALCULATE(COUNTROWS(LogIN))>0
&&
//Never LoogedIN before the Current Date Context
CALCULATE(COUNTROWS(LogIN),FILTER(ALL(Dates),Dates[Date]<MIN(Dates[Date])))=0
)
)

#CWhoLoggedBackIN := [#ContactsWhoLoggedIN]-[#NewCWhoLoggedIN]

#FM_NewCWLI := 
CALCULATE(SUM(Contacts[FamilyMembers]),
FILTER(Contacts,
//LoggedIn in the Current Date Context
CALCULATE(COUNTROWS(LogIN))>0
&&
//Never LoogedIN before the Current Date Context
CALCULATE(COUNTROWS(LogIN),FILTER(ALL(Dates),Dates[Date]<MIN(Dates[Date])))=0
)
)

I remember this pattern from "Microsoft Excel 2013: Building Data Models with PowerPivot"

jprzd
  • 481
  • 2
  • 3