cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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

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.

 

Date problem.PNG

 

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

1 ACCEPTED SOLUTION
Community Champion
Community Champion

@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

 

2016-10-26 - DATEADD Test.png

View solution in original post

3 REPLIES 3
Community Champion
Community Champion

@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

 

2016-10-26 - DATEADD Test.png

View solution in original post

@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.

www.CahabaData.com

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors