YTD and Previous YTD for projects with varying start dates

[JBGyro]

My team takes on customer projects throughout the year and I'd like to see how their sales change while working with us. Each project has it's own start date and I'd like to see sales for the 12 months leading up to that start date, and sales from the 12 months after the start date. I have those formulas below and they seem to be working. For projects that are less than 12 months old, the '12M Sales Since Start' formula works fine as a YTD calculation, but I'm having trouble using SAMEPERIODLASTYEAR or any other command to find the Previous YTD for each project that is less than a year old.

12M Sales Up To Start = 

CALCULATE ( [Revenue], FILTER('DATE', 'DATE'[DATE] >= MIN('Projects'[StartDate]) -365 && 'DATE'[DATE] <= MAX( 'Projects'[StartDate] )))

12M Sales Since Start = 

CALCULATE( [Revenue], FILTER('DATE', 'DATE'[DATE] >= MIN('Projects'[StartDate]) && 'DATE_START'[DATE] <= MAX( 'Projects'[StartDate] ) + 365))

I have a 'Projects' table listing customer name and project start date, which is related to the 'Date' table, which is related to the Sales table via the billing date. How can I use 'Projects'[StartDate] to mark the beginning of the "year" for each project? 

[v-zhouwen-msft]

Hi @Greg_Deckler , @MNedix ,thanks for the quick replies, I'll add further.

Hi @JBGyro ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Measure = 
CALCULATE(SUM('Sales'[Amount]),'Date'[Date] >= MIN('Projects'[StartDate]) - 365  && 'Date'[Date] <= MIN('Projects'[StartDate]),ALL('Projects'[StartDate]))

2.Final output

[JBGyro]

Thank you for this very detailed response! It looks like this is capturing the full year of sales leading up to the project start date, which is what I already have. I'm looking to mimic a YTD view using the project start date. So if a project started on March 1, 2024 and today is May 8, 2024, I would like to see sales from March 1, 2023 to May 8, 2023. Is this possible? 

[v-zhouwen-msft · Accepted solution]

Hi @JBGyro ,

Try this.

Measure 2 = 
VAR _a = MIN('Projects'[StartDate])
VAR _c = SELECTEDVALUE('Projects'[Name])
VAR _b = CALCULATE(SUM('Sales'[Amount]),'Date'[Date] >= _a - 365  && 'Date'[Date] <= TODAY(),ALL('Projects'[StartDate]))
RETURN IF(ISBLANK(_c),BLANK(),_b)

[Greg_Deckler]

@JBGyro Better Year to Date Total - Microsoft Fabric Community

[MNedix]

Hi,

Have you tried DATEADD? For example, since you already have a measure for 12M Sales Since Start then you can try:

12M SSS Last Year = CALCULATE([12M Sales Since Start],DATEADD('Date'[Date],-1,YEAR))

[JBGyro]

Thank you for the reply. I have tried this and it isn't pulling in accurate values, and for some reason it's not pulling any data for projects started this year.