Create Measure based on different row (date)

[Arentir]

Hi guys,

I am trying to create a report on Power BI but can't pin the right way although the goal seems very simple.

Here is my sample data below. I want to create a measure that will do the sum of quantity for the next day (and eventually date +2 and date +3). So I am looking somewhat to override the filter on the date and apply next day instead.

I simplified the sample to two dimensions but I would eventually have more (product category, supplier..)

Sample:

Date	Name	Quantity
01/01/2017	A	5
01/01/2017	A	5
01/01/2017	B	5
02/01/2017	A	1
02/01/2017	A	2
02/01/2017	B	2
02/01/2017	B	3

 What I am trying to achieve:

Date	Name	Sum(Qty)	Sum(Next_Day_Qty)
01/01/2017	A	10	3
01/01/2017	B	5	5
02/01/2017	A	3
02/01/2017	B	5

Thanks 

[ImkeF]

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/ 

[Sean · Accepted solution]

NextDay Totals =
CALCULATE ( SUM ( 'Table'[Quantity] ), DATEADD ( 'Calendar'[Date], +1, DAY ) )

[Arentir]

Sean I believe your solution is close to what I am looking for.

I have written the formulas as :

Spoiler

NextDay 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:

Spoiler

NextDay Total = CALCULATE(SUM('Product'[Quantity]),DATEADD('Calendar'[Date],1,DAY))

The measure looks like a simple Sum(Quantity)

[Sean]

Replace Product[Date] in the Table Visual with Calendar[Date]

[Arentir]

Thanks Sean it was simple as that!