Forum Discussion
changes month over month
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 ...
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 |
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))+0and
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