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

[dphillips]

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...

[v-yuezhe-msft · Accepted solution]

@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]

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!