Forum Discussion
Help creating a measure
Hi gahawy
First add a column year to your datetable then consider using this two measures:
TuitionFees = VAR temptable =
FILTER(
CROSSJOIN(
Students,
DISTINCT('DateTable'[Year])
),
YEAR(Students[AdmissionDate]) <= MIN('DateTable'[Year]) &&
YEAR(Students[ProjectedGraduationDate]) >= MAX('DateTable'[Year])
)
RETURN
SUMX(
temptable,
Students[Yearly tuiton fees]
)
Total Tuition Fees = SUMX(
VALUES('DateTable'[Year]),
[TuitionFees]
)
Use the second one for your calculations:
MFelix Thanks for your help, that is too much appreciated, But I'm a little bit confused as I understand that the TuitionFees measure creates a temporary table with students whose admission and graduation years fall within the specified range (from 'Years' table). so let me explain in details what I'm looking for:
I have one table that is populated with data from our SIS that includes the student ID and his/her admission and supposed graduation year assuming that he/she did not fail, inaddition there is the yearly tution fees.
in this case I wil have students whom are admitted in different years and hence will be supposed to graduated in different years in the future.
I need to draw a chart to show the expected revenue during the upcoming 5 years (less or more) thats why I created a table called DateTable in order to specify (when ediing the years in this table) what years will appear on the chart. I may skip using the DateTable and just use the dates in the main students table (admission date and expected graduation date.
I hope I made myself clear. and once agin I appreciate your answer alot.
Hi gahawy ,
It creates a table with one row for each year of the students admission vs graduation.You must use the Year from the calendar table on your visualizations, that will give you the context for the filtering of the students on those specific years.
The Matrix visualization that is created is wiht the Students ID and the Years(calendar table).
Ok, I did exactly the two measures, and they are giving me no errors, and no output as well, I guess may be I did not understand your instructions in this line " First add a column year to your datetable then consider using this two measures". as I have a datetable with only one coloumn called year, do you mean I need to add another coloumn? and if yes what would the values be? the year as well?