Forum Discussion

dphillips's avatar
dphillips
Helper IV
8 years ago
Solved

Difference between 2 columns in a Matrix with strings as column headings

I have been able to get the differences between columns using dates or integer values as the column headers but I am not sure how to go about this.

 

YearSem is a string. In my slicer I have selected 2 reporting periods - 2016 (T4) and 2017 (T4). The values are a test score. 

 

I would like to see the difference between the two values. For example - for StudentID 49903 I would like a column showing -2. For 49906 I would like to see -9.

 

But - I want to have the flexibility to show the differences when I choose other reporting periods in the slicer and also, if I choose 3 reporting periods in the slicer it gives me the differences between reporting Period 1 to 2 and then reporting period 2 to 3.

 

Not sure how to do this, espcially when the YearSem reporting period is a string and I want the flexibility for the user to choose different reporting periods.

 

Any help would be much appreciated...

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    dphillips,

    Create the following columns in your table.

    Year = LEFT(Table[YearSem],4)
    Column = LEFT(RIGHT(Table[YearSem],2),1)
    Date = DATE(Table[Year],1,Table[Column])


    Create the following measures in your  table.

    PREVIOUSMEASURE = var predate= MAXX(FILTER(ALLSELECTED(Table),Table[Date]<MAX(Table[Date])),Table[Date])
var preID=MAX(Table[StudentID])
return CALCULATE(SUM(Table[score]),FILTER(ALLSELECTED(Table),Table[Date]=predate&&Table[StudentID]=preID)) 
    DIFF = IF(ISBLANK([PREVIOUSMEASURE]),BLANK(),SUM(Table[score])-[PREVIOUSMEASURE])



    Regards,
    Lydia

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    dphillips,

    Create the following columns in your table.

    Year = LEFT(Table[YearSem],4)
    Column = LEFT(RIGHT(Table[YearSem],2),1)
    Date = DATE(Table[Year],1,Table[Column])


    Create the following measures in your  table.

    PREVIOUSMEASURE = var predate= MAXX(FILTER(ALLSELECTED(Table),Table[Date]<MAX(Table[Date])),Table[Date])
var preID=MAX(Table[StudentID])
return CALCULATE(SUM(Table[score]),FILTER(ALLSELECTED(Table),Table[Date]=predate&&Table[StudentID]=preID)) 
    DIFF = IF(ISBLANK([PREVIOUSMEASURE]),BLANK(),SUM(Table[score])-[PREVIOUSMEASURE])



    Regards,
    Lydia

    • dphillips's avatar
      dphillips
      Helper IV
      8 years ago

      Brilliant - thanks so much. I get the date part but still trying to work through exactly how the rest of the measures work. Thanks again for your help!