Forum Discussion

Aroth's avatar
Aroth
Advocate 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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

I think that if you use EXCEPT along with a UNION and a NATURALINNERJOIN that you could essentially return a table with all of your exceptions. So the NATURALINNERJOIN would find the matching rows between the tables. UNION would UNION your tables together and so if you take the EXCEPT of the UNION and NATURALINNERJOIN you would have the differences.

Aroth's avatar
Aroth
Advocate 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
    Community 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
      Advocate 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
        Microsoft 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