Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
@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
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
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |