Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help - Column difference in a matrix

Good afternoon all!

 

I'm in dire need of some help.

 

I have the following matrix making use of 3 fields:

 

  • Description
  • Period
  • Cost

My issue is the following, i want to add a column next to the 2 dates which does a value of 202002 - value of 202001 

 

i want it to only to the difference based on the 2 periods i selected in the filter visual.

 

Any help would be appreciated or guidance on how to do it better.

 

Regards 

 

 

 

Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Create measures

    Measure 2 =
VAR m1 =
    MINX (
        ALLSELECTED ( 'Table 2' ),
        [period]
    )
VAR m2 =
    MAXX (
        ALLSELECTED ( 'Table 2' ),
        [period]
    )
RETURN
    CALCULATE (
        SUM ( 'Table 2'[cost] ),
        FILTER (
            'Table 2',
            'Table 2'[period] = m2
        )
    )
        - CALCULATE (
            SUM ( 'Table 2'[cost] ),
            FILTER (
                'Table 2',
                'Table 2'[period] = m1
            )
        )


Measure = IF(ISINSCOPE('Table 2'[period]),SUM('Table 2'[cost]),[Measure 2])

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Create measures

    Measure 2 =
VAR m1 =
    MINX (
        ALLSELECTED ( 'Table 2' ),
        [period]
    )
VAR m2 =
    MAXX (
        ALLSELECTED ( 'Table 2' ),
        [period]
    )
RETURN
    CALCULATE (
        SUM ( 'Table 2'[cost] ),
        FILTER (
            'Table 2',
            'Table 2'[period] = m2
        )
    )
        - CALCULATE (
            SUM ( 'Table 2'[cost] ),
            FILTER (
                'Table 2',
                'Table 2'[period] = m1
            )
        )


Measure = IF(ISINSCOPE('Table 2'[period]),SUM('Table 2'[cost]),[Measure 2])

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      thank you very much! Works like a charm!

       

      thanks to everyone else who also gave inputs and solutions.

       

      appreciate it!

       

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Make sure period are in a separate table for better working

    Measure =
    var _min = minx(Period,Period[Name])
    var _max = maxx(Period,Period[Name])
    return
    calculate(sum(table[cost]),filter(all(Period),Period[Name]=_min)) -calculate(sum(table[cost]),filter(all(Period),Period[Name]=_max))

     

    if you need more help make me @

    Appreciate your Kudos.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      thanks amitchandak , i tried this and didnt give any errors, but it doesnt want to do the calculation. It just returns a 0.

       

       

  • Nishantjain's avatar
    Nishantjain
    Continued Contributor
    6 years ago

    Anonymous 

     

    Below is a suggestion

     

    Create 3 measures

     

    Measure 1

    Previous Period = 
VAR mindate = min(period)
return
Calculate(Sum(Data[values]), Period[period] = mindate)

     

     

    Measure 2: 

    Latest period= 
VAR maxdate = max(period)
return
Calculate(Sum(Data[values]), Period[period] = maxdate)

     

    Measure 3: 

    Variance = [Measure 1] - [Measure 2]

     

    Drop each of these measures in your matrix and remove the period field from the matrix

     

    Few things to note:

     

    1. If you select more than 3 period, it will automatically default to 2 period selecting the minimum and maximum date

    2. The column headers will not show the period name, but would show the name of the measure