Need help: Dynamic flag with date and period filter

[jacobckso]

I think I have posted it in the wrong section earlier so I will post it here and deleted my old one...

----

Hi Community,

I got stuck with the below questions and please help in giving me some directions.

Context: I have below simplfied tables with key fields which I would need to use in building a dynamic report

Action: [ActionDate],[ActionType],[MemberID]

Member: [MemberID],[CreateDate],[ActivatedDate],[Channel],[Program]

Date: [Day],[Week],[StartofMonth],[EndofMonth]

Questions:

What I need is based on two filters (1) Report Date and (2) Report Period (Monthly / MTD / YTD) and show a table of

New Member: (count of member who created account within the selected period of the report date)

Activated Member: (count of member who activated account within the selected period of the report date)

Retained: (count of member who had any action this year (report date) and have action last year of report date)

Reactivated: (count of member who had any action this year (report date) but no action last year of report date)

Lapse: (count of member who had no action this year (report date) but have action last year)

I am hoping to create this 5 measures as a flag within the member table which would change dynamically when the selected report date and report period had changed, so that I can then further use it for other report such as linking it to CRM and check the members activities response etc.

Please help and let me know how can I build this within the member table and as well as building the date table with dynamic flag for Monthly / MTD / YTD or are there any easier way to do this?

Many thanks in advance for the help and time.

Best Regards,

Jacob

[amitchandak]

@jacobckso , if you join your date table with two tables, these are just count of member id from each table

New Member: (count of member who created account within the selected period of the report date)

Activated Member: (count of member who activated account within the selected period of the report date)

Just choose the count of member id from respective table in place of my sum measure

example

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

MTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LMTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max1), month(_max1)-1, day(_max))
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

now, not transaction measn isblank

if(isblank([YTD],1,0 )

above will give when there no ytd transaction

follow this customer retention code to get your measures

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...