Forum Discussion

coroneltc's avatar
coroneltc
Frequent Visitor
8 years ago

Time intelligence (Each semester in previous year)

Hello everyone,

I am starting in DAX and Power BI and I have some questions about a report that I hope make.

This is my data mart model:

Dimensions:

  • Student: The students of the institution.
  • Time: Stores dates on which students enrolled.
  • Semester: Each year have two semesters. Each semester has date start and date end.
    Example: 2010 year has two semester: 2010-1 and 2010-2

Fact table:

  • Enrollment: Each row is an enrollment some student. A student can enroll a maximum of one time each semester.

I can calculate enrollment previous year with parallel period:

Now I need calculate number enrollments by semester, and each semester can have his enrollment value in previous year.

Example:

In 2011-1 there were 210 enrollments. Its enrollment value in previous year is enrollment value in semester: 2010-1.

 

I tried to combine year, semester(text) and enrollment but is not correct manner to show results.

I attached backup DB and power bi file so you may can try it.

https://drive.google.com/open?id=1mwIjalSalGrLJc-eKq9-0rgxSfyEDeiI

 

Language is spanish so I translate important things here:

Table: Hecho_Matricula = Enrollment fact table.

Table: Tiempo = Time
Table: Semester = Semester

 

In enrollment fact table there is a column called "numero_matriculados". Value in this column is 1.

Basically create this column to make use of the SUM function instead of the COUNT function; but result is same.

 

Many thanks.

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi coroneltc,

     

    "In 2011-1 there were 210 enrollments. Its enrollment value in previous year is enrollment value in semester: 2010-1."

     

    <--- What's your expected requirement? Is your requirement to achieve previous semester's enrollment grouping by each year or some thing else? If possible, could you make a mockup to clarify your problem more explicitly?  

     

    Regards,

    Jimmy Tao

    • coroneltc's avatar
      coroneltc
      Frequent Visitor
      8 years ago

      I made a measure with Paralel Period. Objetive was show how year to year the number of students is maintained.

      If number of students decress in the time it means the organization must reinforce processes (marketing, quality academic, etc) to attract new students.

       

      However, requirement obtained is much greater:

      Each student have a study plan, this study plan has a duration of 5 years in 10 semesters since date of admission (Admission semester). For example: in 2012-1, the students enrolled were: "Jaime", "Pedro" and "María". In 2012-2  the students enrolled were "Jaime" and "Pedro" only. I need show in a table who of the students didn't enroll in selected semester with respect to previous semester. In this case if selected semester is 2012-2 I will get in my table a student record: "María".

       

      In order to solve this requirement, I have crossed information about the student (semester of admission) and semesters. The measure value is the number of enrolled students (each record of the enrollment fact table represents an enrollment of a specific student in some semester):

       

       

      I tried to get information in two tables with FILTER function, in first table to get all students with semester selected. In second table I tried to get students enrolled in previous semester. Finally show Table2 where students NOT IN in Table1. However any effort has been unnecessary because I have not found a solution. 

       

      I would like to know if in Power BI is possible to do these operations at the DAX level, or definitly I need make a report with SQL.

       

      Thanks.