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