Forum Discussion
changes month over month
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 |
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
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.
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.