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

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi thanks for the support, I have the same scenario, when I use the fax in drill through option. It doest show correct caluculation. Can you kindly help what could be the possible issue and why Im not able get the values populated correctly when I used the fax as a column in tablix which is created for drill trhrrough. I have proper relationships with in table. Happy to discuss