Forum Discussion
Resolver IIICreate Measure based on different row (date)
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
NextDay Totals = CALCULATE ( SUM ( 'Table'[Quantity] ), DATEADD ( 'Calendar'[Date], +1, DAY ) )
5 Replies
- ImkeF
Community Champion
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/
SeanNextDay Totals = CALCULATE ( SUM ( 'Table'[Quantity] ), DATEADD ( 'Calendar'[Date], +1, DAY ) )
ArentirSean 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)
