cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pratk30 Regular Visitor
Regular Visitor

Draw a X-axis based on three date column in Line Chart

Hi Team,

 

I have a requirement where i need to draw a Line Chart, for e.g I have two tables - one is Session Table to record each session start and end date.

2nd is Student table to record registration date of student and session id will be foreign key in Student table.

SessionID, Startdate, EndDate are the columns in my Session Table.

StudentID,RegistrationDate,SessionID are columns in  my Student Table.

 

Requirements:

1. Where start and end dates are different for each Session.

2. On Selection of Session, Start and End date will be drawn on X-axis with 15 days interval.

3. Y-axis will show count of Student where Student's registration date is in between start and end date of selected Session.

 

I need to dran a line chart where x-axis will date range starts from StartDate of selected session and ends on Enddate of selected Sesssion and Y-axis will give me count of student where student's registration date is falling between start and end date of sselected session. In Student table, many student can have same registration date.

 

I am just able to draw line chart with student's registration date on X-axis and count on Y-axis from Student Table. I need to show Start and end date as well on X-axis from Session table.

 

Could you please advise me a better approach.

3 REPLIES 3
P3Tom Regular Visitor
Regular Visitor

Re: Draw a X-axis based on three date column in Line Chart

Hi pratk30,

 

What is wrong (what don't you like) with your current approach? 

 

I think there are issues with model design and measures:

 

You can improve the model design by:

 

Separate facts (data) from dimensions (lookups) into different tables.

 

Clarify you data: Are start and end dates against the Session or against the Student or against the Registration (I have a guess, but I should not ever use a guess)?

 

Also, what are the the business questions you are trying to answer with your chart (this will determine the type of measure to write)?

 

Tom

www.powerpivotpro.com

pratk30 Regular Visitor
Regular Visitor

Re: Draw a X-axis based on three date column in Line Chart

Hi Tom,

 

Thanks for response.

I have updated my question, would give a clear scenerio.

 

I have to show a data on line chart where based on date column and this date value shoud be fall between start and end date which is coming from another table. And this start and end date should be visible on X-axis.

Moderator v-sihou-msft
Moderator

Re: Draw a X-axis based on three date column in Line Chart

 

@pratk30

 

I'm not sure if I understand your question clearly. As you said, each student is associated with a Session, you can create a column to tag if current student can be counted as 1. 

 

IfCount= IF(Student[RegistrationDate]>Session[StartDate] && Student[RegistrationDate]>Session[EndDate],1,0)

Then you can sum above column on Session Level to get total count. I think you can use M query to add a custom column, each entry is a Table object which is a list of dates between start date and end date, then expand them for populating on X Axis. 

 

Regards,