Forum Discussion

Aroth's avatar
Aroth
Icon for Advocate II rankAdvocate II
8 years ago
Solved

Show Changes between two given periods

Hi,  I'm working on a report that shows every months the lists of projects we have in our CRM. I would like to show the changes in projects from a period to another but I have some issues. W...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Aroth,

     




    Measure = CALCULATE([net]),FILTER(ALL(MONTHS),MONTHS[index] = MAX(MONTHS[index]))-CALCULATE([net]),FILTER(ALL(MONTHS), MONTHS[index] = MIN(MONTHS[index] )

     

    Based on above measure, I could get the desired output.

     

    What we need is add a MonthNo column to original table (suppose it's 'Month-Project'), and insert an extra table (in my test, it's named as 'Month') listing unique month values.

     

    Create measure like:

    Diff =
CALCULATE (
    ( [net] ),
    FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MAX ( 'Month'[MonthNo] ) )
)
    - CALCULATE (
        ( [net] ),
        FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MIN ( 'Month'[MonthNo] ) )
    )

    Add field [Month] from 'Month' table. Add [Project] and [Diff] from 'Month-Project' table.

     

    Best regards,

    Yuliana Gu

Aroth's avatar
Aroth
Icon for Advocate II rankAdvocate II
8 years ago

Greg_Deckler thank you for answer, I'm not sure how I can use that as everything is on the same data base (table):

MonthProjectnet
OctoberP112
OctoberP25
OctoberP35
DecemberP26
DecemberP34
DecemberP410

 

That's why I use CALCULATE to filter on one month and substract by another CALCULATE filtered on an other month.
And my "net" field is a measure...

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

Wow, I way over complicated that. Just create a measure:

 

Measure = CALCULATE(SUM(Projects[net]),Projects[Month]="December")-CALCULATE(SUM(Projects[net]),Projects[Month]="October")

Then put your Project column and this measure into a Table visualization. 

  • Aroth's avatar
    Aroth
    Icon for Advocate II rankAdvocate II
    8 years ago

    That's what I did, the only thigs Iv'e added is :

    Measure = CALCULATE([net]),FILTER(ALL(MONTHS),MONTHS[index] = MAX(MONTHS[index]))
    -CALCULATE([net]),FILTER(ALL(MONTHS), MONTHS[index] = MIN(MONTHS[index] )

     So it enable to compare two selected periods ...

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi Aroth,

       




      Measure = CALCULATE([net]),FILTER(ALL(MONTHS),MONTHS[index] = MAX(MONTHS[index]))-CALCULATE([net]),FILTER(ALL(MONTHS), MONTHS[index] = MIN(MONTHS[index] )

       

      Based on above measure, I could get the desired output.

       

      What we need is add a MonthNo column to original table (suppose it's 'Month-Project'), and insert an extra table (in my test, it's named as 'Month') listing unique month values.

       

      Create measure like:

      Diff =
CALCULATE (
    ( [net] ),
    FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MAX ( 'Month'[MonthNo] ) )
)
    - CALCULATE (
        ( [net] ),
        FILTER ( 'Month-Project', 'Month-Project'[MonthNo] = MIN ( 'Month'[MonthNo] ) )
    )

      Add field [Month] from 'Month' table. Add [Project] and [Diff] from 'Month-Project' table.

       

      Best regards,

      Yuliana Gu

      • Aroth's avatar
        Aroth
        Icon for Advocate II rankAdvocate II
        8 years ago

        Thanks !!
        I finaly understood my issue. The relation between the two tables had the cross filter selection in both ways!! 
        I changed it to "single" and now it works :)