Forum Discussion
Create Measure based on different row (date)
NextDay Totals = CALCULATE ( SUM ( 'Table'[Quantity] ), DATEADD ( 'Calendar'[Date], +1, DAY ) )
You can use the DATEADD-function to shift your Date-filters forward or backward:
https://msdn.microsoft.com/en-us/library/ee634905.aspx
This requires a separate calendar-table (DateTime), which is one of the best practices for PBI like described here: http://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/
NextDay Totals = CALCULATE ( SUM ( 'Table'[Quantity] ), DATEADD ( 'Calendar'[Date], +1, DAY ) )
Sean I believe your solution is close to what I am looking for.
I have written the formulas as :
SpoilerNextDay Total = CALCULATE(SUM('Product'[Quantity]),DATEADD('Product'[Date],1,DAY))Note that I am using the same table for Quantity and Date. It works like a charm with the current sample, I have tried to add one product C with non following date for test:
It looks fine at first
But when I filter C I get this message
Fair enough, I have tried to add a calendar table relating to Product table and changed the measure to:
SpoilerNextDay Total = CALCULATE(SUM('Product'[Quantity]),DATEADD('Calendar'[Date],1,DAY))The measure looks like a simple Sum(Quantity)
