Variable for Repeatable calculation

[thongnguyen2414]

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

[Anonymous · Accepted solution]

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.

[wdx223_Daniel]

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

[Ehren]

Are you trying to do this in Power Query/M or in DAX? (This is the area of the forum for PQ questions.)