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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
    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 ...

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Can you kindly elaborate more with the simple data set provided by requester. Also can you help how to solve it with drill through