Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

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?

General Comment

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    HI Anonymous

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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 programsnew programslost programs
      1/1/2017220
      2/1/2017200
      3/1/2017200
      4/1/2017200
      5/1/2017211
      6/1/201720 0
      7/1/2017002
      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        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