Forum Discussion

gahawy's avatar
gahawy
New Member
2 years ago

Help creating a measure

Hi,

I have two tables, one contains the data for studenst (id,admission Date, projected graduation date and yearly tuition fees) and the other one contains only years.

I need to calculate the cash flow over the upcoming 5 years in a measure and I tried this

VAR StudentID = SELECTEDVALUE('YourStudentTable'[StudentID])
VAR AdmissionYear = YEAR('DateTable'[Date])
VAR GraduationYear = YEAR('YourStudentTable'[ProjectedGraduationDate])
VAR YearlyFees = 'YourStudentTable'[YearlyTuitionFees]

VAR CashFlow =
IF(AdmissionYear <= AdmissionYear && GraduationYear >= AdmissionYear, YearlyFees, 0)
RETURN
SUMX(
DISTINCT(YourStudentTable[StudentID]),
CashFlow
)

 

With no luck.

 

Any help is too much appreciated.

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    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:

     

    • gahawy's avatar
      gahawy
      New Member
      2 years ago

      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.

      • MFelix's avatar
        MFelix
        Super User
        2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,gahawy Hello,MFelix ,thanks for your concern about this issue.

    If your problem has been solved. If not, are you able to provide some non-sensitive specific information, e.g. in the form of Excel, as I am not sure about the structure of your data table, and it would be helpful if you could provide some simulated data to solve your problem.

    Also I have some questions that I would like you to explain:

    1. For each student, is the annual tuition fixed for that student and does it go up over time

    Is it possible to just go through the "difference in years between expected graduation time minus enrollment time * annual tuition "

    By doing the calculation in this way?

    1. Is the field type of the student's expected graduation time and enrollment time of the DATE type
      like this:
      student ID admission Date projected graduation date yearly tuition fees
      1001 8/20/2020 5/20/2024 30000
      1002 8/20/2020 5/20/2024 30000
      2001 8/20/2021 5/20/2025 35000
      2002 8/20/2021 5/20/2025 35000

    There seems to be something wrong with the Dax code you provided:.

    IF(AdmissionYear <= AdmissionYear && GraduationYear >= AdmissionYear, YearlyFees, 0)

    "AdmissionYear <= AdmissionYear" is a wrong condition.

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.