Next and previous day values

[Anonymous]

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!

[v-jayw-msft · Accepted solution]

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

[amitchandak]

@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

[Anonymous]

I don't have a date table. I have a column on my table which contains date data.

So those functions don't work. They return an empty table. Is this another way to solve this?

Thank you