Forum Discussion
Advocate IIDifference by period column calc
Hi! I'm hoping to create a new column which displays the difference in 'Completed Current Cycle' between one date and the next, by Name in the table below. In other words, for each unique Name I want to see the amount of new 'Completed Current Cycle' each week, because that column is a running total. Any suggestions on a column calculation or other solution?
Thanks!
Hi KMEAGHER
Try using this DAX calculated column (Replace 'YourTableName' with your actual table name):New Completed = VAR CurrentCompleted = [Completed Current Cycle] VAR CurrentDate = [Date] VAR CurrentName = [School Code] VAR PreviousCompleted = CALCULATE( MAX([Completed Current Cycle]), FILTER( ALL('YourTableName'), 'YourTableName'[School Code] = CurrentName && 'YourTableName'[Date] < CurrentDate ) ) RETURN IF(ISBLANK(PreviousCompleted), CurrentCompleted, CurrentCompleted - PreviousCompleted)
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡
3 Replies
- DataVitalizer
Super User
Hi KMEAGHER
Try using this DAX calculated column (Replace 'YourTableName' with your actual table name):New Completed = VAR CurrentCompleted = [Completed Current Cycle] VAR CurrentDate = [Date] VAR CurrentName = [School Code] VAR PreviousCompleted = CALCULATE( MAX([Completed Current Cycle]), FILTER( ALL('YourTableName'), 'YourTableName'[School Code] = CurrentName && 'YourTableName'[Date] < CurrentDate ) ) RETURN IF(ISBLANK(PreviousCompleted), CurrentCompleted, CurrentCompleted - PreviousCompleted)
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡 
KMEAGHERThanks! DataVitalizer I had to make one small tweak, using the Date Rank column rather than the Date column, but it worked! very cool.
- DataVitalizer
Glad it worked, I am happy for you