Count based on condition of 2 dates using OR operator

[electrobrit]

I have data which has customer data and if applicable, member data.
Member is determined by if they have a member # so I first counted how many total members by counting Members with member Total Members=# count(Members(Member#)
Then there is a need to find out of these members which ones are ACTIVE
-active is enrolled in the last 3 months OR a purchase within the last 18 months

I wasn’t sure how to do this so my process began as creating 2 conditional columns
Enrolled in last 3 months= IF(EDATE(‘Members’[Enrollment Date], 3)>TODAY(),1,0)
Purchase within 18 months = if(EDATE(‘Members’[Purchase Date], 18)>TODAY(), 1,0)
then I couldn’t figure out how to get that in it’s own measure with the operator OR.

(maybe there is another way besides using these too)

Active Member= Out of total members, how many are enrolled in last 3 months OR purchased within 18 months
Can someone help with this DAX?
thanks in advance

[electrobrit]

DATA EXAMPLE
name	Member #	Enrolled on	Last purchase		enrolled eligible	purchase eligible
john	1234	1/1/2010	12/1/2018			1
lester	2345	2/18/2018	3/2/2019			1
lucy	3456	5/1/2019	4/3/2019			1
kyle	4567	9/9/2019	1/2/2019		1
bail	3456	7/2/2010	1/1/2019		1
shirly	5678	8/23/2016	2/3/2018		1
tom	1232	4/13/2016	1/1/2019
hank	14567	6/28/2019	12/31/2017
frank	23456	9/1/2019	4/5/2019		1
liza	45678	12/3/2016	1/1/2019			1
penny	81232	6/28/2019	9/6/2019		1
john	null
hank	4949	2/3/2018	9/9/2019			1
loretta	6013	9/9/2019			1
jimmie	3490	9/9/2019			1

note: as of Sept 22, enrollment in last 3 months would be after 6/22/19 
a purchase within 18 months would be after 3/31/18. Active Member falls in one of these.
, 

TOTAL Members with Member #	15
enrolled within 3 months	7
OR (if not enrolled, they made a purchase in last 18 months)
a purchase within 18 months	5
ACTIVE MEMBERS out of this group	12

I can do this in sql but can't figure it out in Power BI.
Select Count (membernumber) from [dbo].[memberTable]
where
enrollmentdate >= DATEADD(MONTH, -3, GETDATE()) or
lastpurchase >= DATEADD(MONTH, -18, GETDATE())

Thank you!

[Nathaniel_C]

Hi @electrobrit ,

First pass.  How conversant are you with Power BI and Power Query? You said you could do this SQL.

Nathaniel

[electrobrit]

still learning how to translate between the 2

[Nathaniel_C]

Lucy and Bail have the same number

[Nathaniel_C]

Hi @electrobrit ,

Here is my table, so as you can see, I added some columns. Although we could have combined these, I left them separate in the interest of understanding and debugging.  I did this in Power Query as it represents less load on your system and it can refresh against your data. So I added a column for today, and one for less three months as well as a conditional to give me 1 or 0. The same with 18 months ago. A final conditional column that gives me a 1 for each active member which I then summed up for the Dax measure. My dates are different, as I used the month function

Let me know if you have any questions, and I can send you my file if you wish. 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[electrobrit]

Ok thanks for the solution. 

I would think all that is a little easier, but I can't get =today() to work in power query. I have looked up different options and oddly having issues finding, which makes me think, the rest of the calcs are going to give me issues. Can you give me your dax used to get to final solution please?  I've been on this issue for a while, perhaps my mind is not working. 
thank you!

[Nathaniel_C]

@electrobrit ,

I will send you the particulars in a couple of mins.

Nathaniel

[Nathaniel_C · Accepted solution]

Hi @electrobrit , @Anonymous ,

Here is the pbix Active Members.

Here is the advanced editor, below. I created some test columns and late deleted them. However if you look closely you will see the code for the various items, like AddMonths. M is very particular on capitalization. You should pick it up quickly.  This forum is a good place for answers. Or google M language, and whatever code you are looking for.  Being able to read technical is really important.

DateTime.Date(DateTime.LocalNow())) is the syntax for Today()

There might be some filters on the table in the ver that I sent you. So you can turn them off just like in Excel at the top of the column.

The only measure I used was summing up the count. 

Active members = SUM('members'[New or Active])

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

let
    Source = Excel.Workbook(File.Contents("C:\Users\nat_c\OneDrive\Desktop\if emp purch.xlsx"), null, true),
    purchasemember_Table = Source{[Item="purchasemember",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(purchasemember_Table,{{"name", type text}, {"Member #", Int64.Type}, {"Enrolled on ", type date}, {"Last purchase", type date}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Member #", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Custom", each if [#"Enrolled on "] < #date(2019, 9, 22) then 1 else 0),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Last purchase] < #date(2019, 9, 22) then 1 else 0),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column1", "Custom.2", each DateTime.Date(DateTime.LocalNow())),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.3", each Date.AddMonths(Date.From([Custom.2]), Number.From(-3
))),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Custom1", "Custom.4", each if [#"Enrolled on "] >= [Custom.3] then 1 else 0),
    #"Added Custom2" = Table.AddColumn(#"Added Conditional Column2", "Custom.5", each Date.AddMonths(Date.From([Custom.2]), Number.From(-18))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom2",{{"Custom.2", "Today"}, {"Custom.3", "90 days ago"}, {"Custom.5", "540 days ago"}}),
    #"Added Conditional Column3" = Table.AddColumn(#"Renamed Columns", "Active Purchase", each if [Last purchase] >= [540 days ago] then 1 else 0),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Conditional Column3",{{"Custom.4", "Active new"}}),
    #"Added Conditional Column4" = Table.AddColumn(#"Renamed Columns1", "Custom.2", each if [Active new] = 1 then 1 else if [Active Purchase] = 1 then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column4",{"Custom", "Custom.1"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Custom.2", "New or Active"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Today", type date}, {"90 days ago", type date}, {"Active new", Int64.Type}, {"540 days ago", type date}, {"Active Purchase", Int64.Type}, {"New or Active", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"name", Order.Ascending}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Sorted Rows",{{"540 days ago", "18 months ago"}, {"90 days ago", "3 Months ago"}})
in
    #"Renamed Columns3"

[electrobrit]

thank you, I did get "today". If you got it, would like to see what you did. i am new to dax

[Nathaniel_C]

Hi @electrobrit , 

So I showed the data on a matrix, and sorted the enrolled on date from the top. The line shows above are new enrollees, and below are the active purchasers.  I count 13.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Hi,

can you share a sample of your data?

Thanks.