Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dphillips
Helper IV
Helper IV

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.

Capture.JPG

 

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

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

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

1.JPG2.JPG

Regards,
Lydia

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

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

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

1.JPG2.JPG

Regards,
Lydia

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

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.