Hello boardmembers.
This is my first post in an Excellent (sorry about the pun!) Message Board. I need help or guidance because I am stuck in constructing a model:
I am using Excel 2013 Professional Plus.
I have a database running from the year 2001 to the year 2014 with participants in a program which has a duration between 12 to 24 months.
My goal is to make a model to calculate different KPI's and to analyze it using PowerPivot and PowerView.
The database contains the following data:
Name of the participant
Startdate of program
Enddate of program
Startdate of activity 1
Enddate of activity 1
The model aimes to show month over month difference and year over year difference for the following KPI's:
1. Distinct count of participants engaged in activity 1 for a spesific time (for instance 2014 August)
2. No of days for each of the participants for each month in the report period
3. No of days for each participant divided by the total number of participants in the program (percentage) - total and for each month in the report period.
In other words, I want to analyze the data and present them for the report year 2014 and compare the numbers with the report year 2013 (or any other two years.)
I have been able to set up the data and I have loaded it into PowerPivot and added it to the data Model.
Although known in the Excel formula universe, DAX is new to me. I just can't seem to figure out how to set the calculations up.
Should I use a calendar table for the specific period I want to report (for instance 2014.01.01 to 2014.12.31) and then use
a calculated field with a logical test for each month's occuriences? And if this is the case, should I use the CALCULATE - function or the specific date and
time calculating DAX formulas.
Thank You in advance for all tips making me a little closer to my goal: sending my boss to his knees in jawdropping awe.
Best regards
Thomas Jenssen, Norway.
This is my first post in an Excellent (sorry about the pun!) Message Board. I need help or guidance because I am stuck in constructing a model:
I am using Excel 2013 Professional Plus.
I have a database running from the year 2001 to the year 2014 with participants in a program which has a duration between 12 to 24 months.
My goal is to make a model to calculate different KPI's and to analyze it using PowerPivot and PowerView.
The database contains the following data:
Name of the participant
Startdate of program
Enddate of program
Startdate of activity 1
Enddate of activity 1
The model aimes to show month over month difference and year over year difference for the following KPI's:
1. Distinct count of participants engaged in activity 1 for a spesific time (for instance 2014 August)
2. No of days for each of the participants for each month in the report period
3. No of days for each participant divided by the total number of participants in the program (percentage) - total and for each month in the report period.
In other words, I want to analyze the data and present them for the report year 2014 and compare the numbers with the report year 2013 (or any other two years.)
I have been able to set up the data and I have loaded it into PowerPivot and added it to the data Model.
Although known in the Excel formula universe, DAX is new to me. I just can't seem to figure out how to set the calculations up.
Should I use a calendar table for the specific period I want to report (for instance 2014.01.01 to 2014.12.31) and then use
a calculated field with a logical test for each month's occuriences? And if this is the case, should I use the CALCULATE - function or the specific date and
time calculating DAX formulas.
Thank You in advance for all tips making me a little closer to my goal: sending my boss to his knees in jawdropping awe.
Best regards
Thomas Jenssen, Norway.
