Hi there.  I think I have this figured out. I used your data.  These are the steps that I took: I used each of the items as a category. I created a new custom item that concatenated the faculty ID and the student ID Fac ID - St ID = Concatenate(Format('FacultyID'n[Raw], 'BEST4.'), Format('Student ID'n[Raw], 'BEST4.')) I created 3 aggregated measures from Fac ID - St ID (you could do this with one) Count = Count [_ByGroup_] ('Fac ID - St ID'n) Distinct = Distinct [_ByGroup_] ('Fac ID - St ID'n) Avg = 'Count'n / 'Distinct'n I was able to create the results you were looking for.   Cheers, Roger ... View more

There are two ways I have done this.  Each is to have a Category called "Fiscal Year" If you are using 7.1, you can create a custom category based off of existing categories.  In the screen shot, I based my custom category off of "Occupancy Month" which are calendar months.  In this case I can assign any months that I want to any Fiscal Year.  NOTE:  Occupancy Month is a duplicate of Occupancy Date and formatted in a Month Format. If you are using a version prior to 7.1, you can add a column in the upload or the column editor of Fiscal Year.  This column should have an entry for each of the most granular date items. When reporting on the category Fiscal Year, your data will follow it's aggregation rules (Sum, Max, Avg, etc) ... View more

Hi. One way to do this is to duplicate your date item and format it as a year. Say you have an item called "Date" and it is daily.  Right click on it, select duplicate. Click on the new one and change the format (and the name) to Year (or month) Use the new item in the report, and your data items will follow their aggregation methods assigned to them (average, sum, etc...) For a financial year it is a little harder.  What I have done is load a column of data that assigns each Date to a financial year, quarter, etc.  Then report on those columns. Good Luck, Roger ... View more

Hi. I did something similar.  In my case I have 2 dates : A system date of the data (called 'latestbusinessdayend') and a hotel occupancy date. (called 'occupancyDate'). I wanted a way to set a report to only look at say, 7 - 14 days from today or the prior 7 - 14 days.  The formula below creates a series of numbers (from negative to positive based on how far the occupancy date is from the system date) I was able to accomplish this using the TreatAs function that is in version 6.4 and higher:  Note:  I did have a version before 6.4 that did this but was way more complex.... TreatAs(_Number_, 'occupancyDate'n) - TreatAs(_Number_, 'latestBusinessDayEnd'n) To implement this, use a date selector of choice then use this as a filter on your dates.  For example, the filter below will set the dates to be selected from +1 to +7 ( 'Days from LBDE TreateAS'n > 1 ) AND ( 'Days from LBDE TreateAS'n <= 7 ) In my opinion the date slider works best for selections as it is pretty easy for the user.   If you want to make it smaller, you can put a non-selectable text box next to it. Good Luck,  Roger ... View more