Date Difference by Items First Ship Date

[calen]

Good Morning Everyone!

I am trying to create a BI report to tracks the success of new projects. Sales gives me their projected volumes for years 1, 2, and 3. I need a measure that looks at the first ship date (Posting Date) for the selected item and then groups it into year 1, 2, or 3. I tried 

Day Difference = DATEDIFF(MIN('Item Ledger Entries'[Posting Date]), AVERAGE('Item Ledger Entries'[Posting Date]),Day). From there I can just group days into their respective year. The problem is it keeps looking at the minimum posting date for all projects, not a specific project. 

Any thoughts on how I can get this working?

Thanks!

[jdbuchanan71 · Accepted solution]

@calen 

I'm assuming you want this as a calculated column in your table.  That would be something like this.

Days = 
VAR _ThisDate = 'Item Ledger Entries'[Posting Date]
VAR _ProjStart = CALCULATE ( MIN ( 'Item Ledger Entries'[Posting Date] ), ALLEXCEPT ( 'Item Ledger Entries', 'Item Ledger Entries'[Project] ) )
RETURN
    DATEDIFF ( _ProjStart, _ThisDate, DAY )

[calen]

This worked beautifuly! Thank you so much!