changes month over month

[Anonymous]

Hello, i am having trouble with the following data set.

I have data that is organized in the following way.

Program Num	active month
a	1/1/2017
a	2/1/2017
a	3/1/2017
a	4/1/2017
b	5/1/2017
b	6/1/2017
c	1/1/2017
c	2/1/2017
c	3/1/2017
c	4/1/2017
c	5/1/2017
c	6/1/2017

Throughout this data, i have 2 unique program num's active at any time.  So if i trend by Distinct program num, i get the value of 2 from Jan-Jun.  No problem here.  This issue is I also need to show...

1. in May, B appeared on the list (gains)
2. in May, A was disappeared from the list. (losses)

This needs to happen in DAX as there are multiple dimensions the users need to select to understand the gains, losses and overall change in counts

I was able to accomplish displaying the values based on the data model, but i cannot figure out how to need to trend the gains/losses.

Any ideas?

[Phil_Seamark]

HI @Anonymous

Do you have an idea on your desired output?  I'm not clear from your post, what you are after. 

[Anonymous]

Here would be the desired results, so they i could graph this.

if you look at the data from the original post, you can interpret it as this...

- program a is active from Jan-Apr, was new in Jan and lost in May (i.e. Apr was the last active month)

- program b is active from May-Jun, was new in Apr and lost in July (i.e. Jun was the last active month(

- program c is active from Jan-Jun, was new in Jan and lost in July (i.e. Jun was the last active month(

	active programs	new programs	lost programs
1/1/2017	2	2	0
2/1/2017	2	0	0
3/1/2017	2	0	0
4/1/2017	2	0	0
5/1/2017	2	1	1
6/1/2017	2	0	0
7/1/2017	0	0	2

[Vvelarde]

@Anonymous

Lets try with this 2 measures:

New Programs =
SUMX (
    SUMMARIZE (
        Table1;
        Table1[Program Num];
        "New"; IF (
            CALCULATE (
                MIN ( Table1[Active Month] );
                ALLEXCEPT ( Table1; Table1[Program Num] )
            )
                = SELECTEDVALUE ( ActiveMonths[ActiveMonth] );
            1;
            0
        )
    );
    [New]
)

Lost Programs =
SUMX (
    SUMMARIZE (
        Table1;
        Table1[Program Num];
        "Lost"; IF (
            CALCULATE (
                MAX ( Table1[Active Month] );
                ALLEXCEPT ( Table1; Table1[Program Num] )
            )
                = EDATE ( SELECTEDVALUE ( ActiveMonths[ActiveMonth] ); -1 );
            1;
            0
        )
    );
    [Lost]
)

Assuming you have a table with the ActiveMonths.

And a Visual like:

Regards

Victor

[Anonymous]

Victor,

Thank you for your help.  I have 2 follow up questions.

1. I can't seem to get the "lost" DAX to work.  It is producing all zeroes of each month.  Am i missing something?

2. I got the "new" DAX to work, but with 1 issue.  If a program is active, then is lost, and then months later is active again, we want to count that as new again.  for example, in the original data set, say you add a new row for program "A" with an active month in August 2017...it doesn't acknowledge August as a new date.

Thank you for your help so far.  This is a huge help.

[Anonymous]

Victor,

I figured out the problem with the lost accounts.  The "EDATE" function should not be applied.

so my only issue at the moment is if there 1 program is "new" or "lost" multiple times.  

[Phil_Seamark]

HI @Anonymous

I got pretty close with the following two calculated meaures

New Programs = 
VAR ThisMonth =SELECTCOLUMNS(FILTER('Table','Table'[active month] = MAX('Table'[active month])),"P",[Program Num]) 
VAR LastMonth= SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[active month] = EDATE(MAX('Table'[active month]),-1)),"P",[Program Num])
RETURN COUNTROWS(EXCEPT(ThisMonth,LastMonth))+0

and

Lost Programs = 
VAR ThisMonth =SELECTCOLUMNS(FILTER('Table','Table'[active month] = MAX('Table'[active month])),"P",[Program Num]) 
VAR NextMonth1= SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[active month] = EDATE(MAX('Table'[active month]),-1)),"P",[Program Num])
RETURN COUNTROWS(EXCEPT(NextMonth1,ThisMonth))+0