Forum Discussion
Daily changes
earlier i had posted this question but did not explain properly My knowledge is not that great in power bi, learning from all you guys. I tried solving it but cannot get it. Higly appreciate for taking time to reply. Thank you every one.
I have daily stock item wise rate and units, where i a measure which gives the result. the issue is i have matrix which shows itemwise changes, Very IMP - it works only when i select the date in the slicer, when i try to put the date in the slicer it gives an error message. Please note the previous date value and the current value is based on date selection.
Want the result like this, i get this result only when i select individual date, i cannot show a trend of the days
| Product | 02-06-2024 | 03-06-2024 | 04-06-2024 | 05-06-2024 |
| Bike | 5 | 12.5 | 5.586 | -1 |
| Cycle | 0.12 | 15 | -10.05 | 2.5 |
| Pen | -4.48 | 7 | 12.81 | 4.5 |
| Car | 0 | 7.5 | 13.2 | 4.5 |
| Toy | 53.4 | 12 | -11.4 | 4 |
| Pencil | -15.6 | 11 | 25 | -1 |
| Product | Date | Rate | Units | Unit Change (A) | Rate Change (B) | Final Result (A X B) |
| Bike | 31-05-2024 | 10.00 | 100 | |||
| Cycle | 31-05-2024 | 20.00 | 154 | |||
| Pen | 31-05-2024 | 30.00 | 160 | |||
| Car | 31-05-2024 | 40.00 | 210 | |||
| Toy | 31-05-2024 | 50.00 | 230 | |||
| Pencil | 31-05-2024 | 60.00 | 260 | |||
| Bike | 02-06-2024 | 10.50 | 110 | 10 | 0.50 | 5.00 |
| Cycle | 02-06-2024 | 20.12 | 155 | 1 | 0.12 | 0.12 |
| Pen | 02-06-2024 | 31.12 | 156 | -4 | 1.12 | -4.48 |
| Car | 02-06-2024 | 42.15 | 210 | 0 | 2.15 | 0.00 |
| Toy | 02-06-2024 | 51.78 | 260 | 30 | 1.78 | 53.40 |
| Pencil | 02-06-2024 | 60.78 | 240 | -20 | 0.78 | -15.60 |
| Bike | 03-06-2024 | 11.00 | 135 | 25 | 0.50 | 12.50 |
| Cycle | 03-06-2024 | 20.62 | 185 | 30 | 0.50 | 15.00 |
| Pen | 03-06-2024 | 31.62 | 170 | 14 | 0.50 | 7.00 |
| Car | 03-06-2024 | 42.65 | 225 | 15 | 0.50 | 7.50 |
| Toy | 03-06-2024 | 52.28 | 284 | 24 | 0.50 | 12.00 |
| Pencil | 03-06-2024 | 61.28 | 262 | 22 | 0.50 | 11.00 |
| Bike | 04-06-2024 | 11.80 | 142 | 7 | 0.80 | 5.59 |
| Cycle | 04-06-2024 | 22.63 | 180 | -5 | 2.01 | -10.05 |
| Pen | 04-06-2024 | 33.45 | 177 | 7 | 1.83 | 12.81 |
| Car | 04-06-2024 | 44.85 | 231 | 6 | 2.20 | 13.20 |
| Toy | 04-06-2024 | 57.98 | 282 | -2 | 5.70 | -11.40 |
| Pencil | 04-06-2024 | 63.78 | 272 | 10 | 2.50 | 25.00 |
| Bike | 05-06-2024 | 12.30 | 140 | -2 | 0.50 | -1.00 |
| Cycle | 05-06-2024 | 23.13 | 185 | 5 | 0.50 | 2.50 |
| Pen | 05-06-2024 | 33.95 | 186 | 9 | 0.50 | 4.50 |
| Car | 05-06-2024 | 45.35 | 240 | 9 | 0.50 | 4.50 |
| Toy | 05-06-2024 | 58.48 | 290 | 8 | 0.50 | 4.00 |
| Pencil | 05-06-2024 | 64.28 | 270 | -2 | 0.50 | -1.00 |
Hi, santoshlearner2
You can use matrix to acheive the arm.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Dear Yang,
it is nice of you to reply, i truly appreciate it. The column marked in brackets viz A & B, ie
Unit Change (A) Rate Change (B) Final Result (A X B) This is derieved from minus the previous day values, which i get from a measure like current minus the previous etc. This issue is when i put the date in the slicer the values are correctly depicting, but if i put the same in the matrix, ( Since no date is selected) . It gives any error.
Thank you in advance for your much need assistance.
Hi, I believe the measures you used causes it.
I would handle them with calculated columns as follow:
1st:
Daily Change =
VAR PreviousDayUnits =
CALCULATE(
MAX(TableName[Units]),
FILTER(
TableName,
TableName[Product] = EARLIER(TableName[Product]) &&
TableName[Date] = EARLIER(TableName[Date]) - 1
)
)
RETURN
IF(
ISBLANK(PreviousDayUnits),
BLANK(),
TableName[Units] - PreviousDayUnits
)
---
2nd:Final Result = TableName[Daily Change]*TableName[Rate Change]
Data table would look like this
And the matrix will work regardless of slicer selection:Thank you, i appreciate your reply. Hats off to you
6 Replies
sorry an addition to the this is when i use the date in the matrix it does not work
Dear All,
Any luck on the above.
Warm RegardsHi, santoshlearner2
You can use matrix to acheive the arm.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Dear Yang,
it is nice of you to reply, i truly appreciate it. The column marked in brackets viz A & B, ie
Unit Change (A) Rate Change (B) Final Result (A X B) This is derieved from minus the previous day values, which i get from a measure like current minus the previous etc. This issue is when i put the date in the slicer the values are correctly depicting, but if i put the same in the matrix, ( Since no date is selected) . It gives any error.
Thank you in advance for your much need assistance.
Hi, I believe the measures you used causes it.
I would handle them with calculated columns as follow:
1st:
Daily Change =
VAR PreviousDayUnits =
CALCULATE(
MAX(TableName[Units]),
FILTER(
TableName,
TableName[Product] = EARLIER(TableName[Product]) &&
TableName[Date] = EARLIER(TableName[Date]) - 1
)
)
RETURN
IF(
ISBLANK(PreviousDayUnits),
BLANK(),
TableName[Units] - PreviousDayUnits
)
---
2nd:Final Result = TableName[Daily Change]*TableName[Rate Change]
Data table would look like this
And the matrix will work regardless of slicer selection:
Thank you, i appreciate your reply. Hats off to you