Add DATESBETWEEN measure based on 2 date columns

[eOfer]

Hi,

How do i add a Subscriber measure based on valid dates per period between Effective_Date and Expiration_Date?

Thanks!

[Phil_Seamark]

Hi @eOfer

What would your expected result be for the top row where Subscriber is 0528889991?

[eOfer]

Hi @Phil_Seamark,

The aim of my question is to show a tendency of active subscribers per chosen period.

If 0528889991 is within the chosen period - count it and display on the graph.

[Phil_Seamark]

HI @eOfer

Do you want to plot daily, weekly or monthly usage?

[eOfer]

Monthly

[Phil_Seamark]

and one last request,  Any chance you can paste your sample data here to save me typing it all in 🙂

[eOfer]

Sure, 

Here's a sample:

Subscriber	Service_code	Effective_Date	Expiration_Date
0528889991	ABC	1/1/2017	3/8/2017
0528889992	DEF	5/1/2017
0528889993	CCC	3/1/2018	5/26/2018
0528889994	ABC	3/24/2018	6/1/2018
0528889995	DEF	1/30/2017
0528889996	XYZ	2/4/2017	4/29/2017
0528889997	ABC	6/4/2017	8/29/2017
0528889998	BBB	4/4/2018
0528889999	XYZ	4/27/2018	6/28/2018
0528890000	ABC	3/4/2017	5/21/2017
0528890001	DEF	3/9/2017
0528890002	XYZ	7/8/2017	10/2/2017
0528890003	AAA	5/8/2018
0528890004	DEF	5/31/2018
0528890005	CCC	4/7/2017	7/6/2017
0528890006	ABC	4/12/2017	7/12/2017
0528890007	BBB	8/11/2017	11/7/2017
0528890008	XYZ	6/11/2018
0528890009	ABC	7/4/2018	9/12/2018
0528890010	DEF	5/11/2017
0528890011	XYZ	5/16/2017	7/20/2017
0528890012	ABC	9/14/2017	12/2/2017
0528890013	DEF	7/15/2018	9/26/2018

[Phil_Seamark]

Hi @eOfer

I created the following calculated table using this code, and have attached a PBIX file that uses your sample data.

Expanded Table = 
var myMonths = SUMMARIZE(ADDCOLUMNS( CALENDARAUTO(),"Month",DATEVALUE(FORMAT([Date],"1-MMM-YYYY"))),[Month])
RETURN 
    GENERATE(
        'Table1',
        FILTER(
            myMonths,
            [Month]>=[Effective_Date] && [Month] < IF(ISBLANK([Expiration_Date]),DATE(2099,1,1),[Expiration_Date])
            )
            
            )

[eOfer · Accepted solution]

Thanks a lot @Phil_Seamark!

Awesome!

[eOfer]

Hi Guys,

I'm trying to figure out from the table how many active subscribers do i have between start date (Effective_Date) and present day, considering their formal end date (Expiration_Date). 

Any thoughts will be much appreciated!

Thanks,

Ofer Eavri

Active Users