Use start date to calculate first 7, 14, 30 days etc

[Betsy]

Hi All,

I am trying to create a report for the first day, 14 days, 30 days of service.

I have a Students table that lists student ID, Organization, University ,Cohort etc, and a Package Table that lists the date a text was sent to students, among other things.

I have created a measure that pulls the start date as follows: Start Date = FIRSTDATE(Packages[Package Date]).

I can see the start dates listed for each student ID (or organization/cohort etc) in a table when I create one in a report, so the measure seems to be working correctly.

I want to use this to look at the number of stops, responses, and other things that I have already calculated for the first Day, first 14 days, first 30 days for each organization. 

I tried a calculated table, first pulling Distinct Student IDs from the Student Table, and after creating a relationship with the packages table, adding a column with the Start Date measure. Seems to work.

I then tried adding columns, with DATEADD. The dates are not being added for all start dates.

What am I doing wrong? There are other relationships for the Package Table, and the Package Table is also connected to a date table. Could it be something there? Is there another way to look at time periods with different start dates without calculating this kind of table?

Thanks for your help!

Betsy

[Sean · Accepted solution]

@Betsy Why don't you just add the +7 ?

First 7 Days = 'Start Dates'[Start Date] + 7

DATEADD returns a TABLE of dates

https://msdn.microsoft.com/en-us/library/ee634905.aspx

EDIT: You can make it work (don't know why you would want to though?) but you need to reference your Calendar Table.

DATEADD works with contiguous date selection

"The Main reason why a Date Table should include all days within a year is because of DATEADD behavior"

The Definitive Guide to DAX page 194

[CahabaData]

@Sean thanks for pointing out the DateAdd aspect on requiring contiguous numbering.  I never realized that.  I probably would default just use the +7 method as you suggested, but it is good to know about the DateAdd issues.

[Betsy]

Thank you @Sean!! That's what I was doing wrong. I had a date table, just did the DATEADD with the date in my Start Date column, not to the date table date as you've suggested. That second solution worked perfectly.

The add 7 added 7 to the student ids with no start date, by adding 7 to the first date ever (1/1/1900) so that would have worked if all students had a start date.