Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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