Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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...
Solved! Go to 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,
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
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!
User | Count |
---|---|
72 | |
70 | |
37 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |