Forum Discussion
Next and previous day values
Hello to PowerBI community,
I have 2 tables
1st
| Code | Description |
| 1 | x |
| 2 | y |
| 3 | z |
| 4 | w |
2nd
| Date | Code | Value |
| 3/5/2021 | 1 | 58 |
| 3/5/2021 | 2 | 65 |
| 3/5/2021 | 3 | 12 |
| 3/5/2021 | 4 | 43 |
| 3/6/2021 | 2 | 87 |
| 3/6/2021 | 3 | 54 |
| 3/6/2021 | 4 | 12 |
I had made a relationship on code in order to have this picture
| Date | Code | Value | Description |
| 3/5/2021 | 1 | 58 | x |
| 3/5/2021 | 2 | 65 | y |
| 3/5/2021 | 3 | 12 | z |
| 3/5/2021 | 4 | 43 | w |
| 3/6/2021 | 2 | 87 | y |
| 3/6/2021 | 3 | 54 | z |
| 3/6/2021 | 4 | 12 | w |
| 3/7/2021 | 1 | 0 | x |
| 3/7/2021 | 2 | 0 | y |
| 3/7/2021 | 3 | 0 | z |
| 3/7/2021 | 4 | 0 | w |
I don't want to use filter relative date. I want to create a measure display next day value and if next day values are 0 display previous day. As you can see 3/7/2021 value column are all 0 so I want to see previous day (3/6/2021) values. Also keep in mind that I want to use in my table all the columns except date column and my data connectivity mode is Import.
Thank you in advance for your help!
Hi Anonymous ,
Check the measure and the result.
Measure = var _next = CALCULATE(SUM('Table 2'[Value]),FILTER(ALL('Table 2'),'Table 2'[Date]=SELECTEDVALUE('Table 2'[Date])+1&&'Table 2'[Code]=SELECTEDVALUE('Table 1'[Code]))) var _previous = CALCULATE(SUM('Table 2'[Value]),FILTER(ALL('Table 2'),'Table 2'[Date]=SELECTEDVALUE('Table 2'[Date])-1&&'Table 2'[Code]=SELECTEDVALUE('Table 1'[Code]))) return IF(_next=0,_previous,_next)If the result is not what you want, please show the expected result.
Best Regards,
Jay
3 Replies
- amitchandak
Super User
Anonymous , with a date table you can use time intelligence
Last Day = CALCULATE(sum('Table'[Value]), previousday('Date'[Date]))
nextDay = CALCULATE(sum('Table'[Value]), nextday('Date'[Date]))
Other examples
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9 Hi Anonymous ,
Check the measure and the result.
Measure = var _next = CALCULATE(SUM('Table 2'[Value]),FILTER(ALL('Table 2'),'Table 2'[Date]=SELECTEDVALUE('Table 2'[Date])+1&&'Table 2'[Code]=SELECTEDVALUE('Table 1'[Code]))) var _previous = CALCULATE(SUM('Table 2'[Value]),FILTER(ALL('Table 2'),'Table 2'[Date]=SELECTEDVALUE('Table 2'[Date])-1&&'Table 2'[Code]=SELECTEDVALUE('Table 1'[Code]))) return IF(_next=0,_previous,_next)If the result is not what you want, please show the expected result.
Best Regards,
Jay
