Forum Discussion
Variable for Repeatable calculation
Hello
- I really need your advices about the variable for something like Repeatable calculation ( I have no idea if I can call it like this ^^ )
- I am in trouble to calculate column/data for the 'Previous Date. Operation WIP' because :
1. The 'Previous Date.Operation WIP' for today = Today Operation WIP (yesterday) + Previous Date. Operation WIP (yesterday)
2. For the 'Today Operation WIP' (yesterday, we can calculate. However; I have no idea how to calculate the 'Previous Date. Operation WIP' (yesterday) because it also is depended on the data of the day before yesterday (with the same logic)
For example:
Please kindly help to look at the attached photo which I have explained. Thank you.
Here is my data
| Date | Line | File No | Description | Op. No | Q'ty |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 1 | 760 |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 2 | 1398 |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 3 | 1664 |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 4 | 1715 |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 5 | 1783 |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 6 | 1758 |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 7 | 1508 |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 8 | 1434 |
| 6/1/2021 0:00 | LINE 5 | VSCG213604 | 94 | 9 | 1444 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 1 | 2155 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 2 | 2304 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 3 | 1811 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 4 | 1773 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 5 | 1950 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 6 | 1867 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 7 | 1961 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 8 | 1737 |
| 6/2/2021 0:00 | LINE 5 | VSCG213604 | 94 | 9 | 2070 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 1 | 2279 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 2 | 2131 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 3 | 1926 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 4 | 1891 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 5 | 2052 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 6 | 2112 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 7 | 2046 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 8 | 2097 |
| 6/3/2021 0:00 | LINE 5 | VSCG213604 | 94 | 9 | 2026 |
Hi thongnguyen2414 ,
I'd suggest you create measures instead:
_1 = var _pre= CALCULATE(SUM('Table'[Q'ty]),FILTER(ALLSELECTED('Table'),[Date]=MAX('Table'[Date])&& [Op. No]=MAX('Table'[Op. No])-1)) return IF(_pre=BLANK(),BLANK(), SUM('Table'[Q'ty]) -_pre)_2 = CALCULATE([_1],PREVIOUSDAY('Table'[Date]))_3 = [_1]+[_2]_5 = CALCULATE([_3],PREVIOUSDAY('Table'[Date]))_6 = [_1]+[_5]The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Are you trying to do this in Power Query/M or in DAX? (This is the area of the forum for PQ questions.)
is it the logic?
1, Column 6 always equal the total amount until today
2, Column 5 is the total amount until yesterday
3, Column 4 is Column6 - Column5
Hi thongnguyen2414 ,
I'd suggest you create measures instead:
_1 = var _pre= CALCULATE(SUM('Table'[Q'ty]),FILTER(ALLSELECTED('Table'),[Date]=MAX('Table'[Date])&& [Op. No]=MAX('Table'[Op. No])-1)) return IF(_pre=BLANK(),BLANK(), SUM('Table'[Q'ty]) -_pre)_2 = CALCULATE([_1],PREVIOUSDAY('Table'[Date]))_3 = [_1]+[_2]_5 = CALCULATE([_3],PREVIOUSDAY('Table'[Date]))_6 = [_1]+[_5]The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
