Forum Discussion
Solved
Date Difference by Items First Ship Date
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!
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 )
2 Replies
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 )This worked beautifuly! Thank you so much!
