Using variable to calculate data of previous date & current date

[thongnguyen2414]

Greetings all,

and Have a nice Monday 😚

1. I am in need of calculate data of current date, but need to pick up result form the previous date.

(Because my data is too much with variety of date, Line, File No, Description, and they are also 04 slicers which have to be selected for filter & I have just pick up 1 line to make an example for forcusing 🙂)

2. From the initial data table, I would like to calculate 3 kind of column are PREVIOUS, TODAY & ACCUMULATATION.

3. I have done to calculate TODAY Operation WIP as below measure that I have learnt from @Vera_33  ^^, but meet challanges to calculate the two others.

My initial data table is:

Date	Line	File No	Description	Op. No	Q'ty
6/1/2021	LINE 5	VSCG213604	94	1	760
6/1/2021	LINE 5	VSCG213604	94	2	1398
6/1/2021	LINE 5	VSCG213604	94	3	1664
6/1/2021	LINE 5	VSCG213604	94	4	1715
6/1/2021	LINE 5	VSCG213604	94	5	1783
6/1/2021	LINE 5	VSCG213604	94	6	1758
6/1/2021	LINE 5	VSCG213604	94	7	1508
6/1/2021	LINE 5	VSCG213604	94	8	1434
6/1/2021	LINE 5	VSCG213604	94	9	1444
6/2/2021	LINE 5	VSCG213604	94	1	2155
6/2/2021	LINE 5	VSCG213604	94	2	2304
6/2/2021	LINE 5	VSCG213604	94	3	1811
6/2/2021	LINE 5	VSCG213604	94	4	1773
6/2/2021	LINE 5	VSCG213604	94	5	1950
6/2/2021	LINE 5	VSCG213604	94	6	1867
6/2/2021	LINE 5	VSCG213604	94	7	1961
6/2/2021	LINE 5	VSCG213604	94	8	1737
6/2/2021	LINE 5	VSCG213604	94	9	2070
6/3/2021	LINE 5	VSCG213604	94	1	2279
6/3/2021	LINE 5	VSCG213604	94	2	2131
6/3/2021	LINE 5	VSCG213604	94	3	1926
6/3/2021	LINE 5	VSCG213604	94	4	1891
6/3/2021	LINE 5	VSCG213604	94	5	2052
6/3/2021	LINE 5	VSCG213604	94	6	2112
6/3/2021	LINE 5	VSCG213604	94	7	2046
6/3/2021	LINE 5	VSCG213604	94	8	2097
6/3/2021	LINE 5	VSCG213604	94	9	2026

Today WIP = 
VAR CurOp = Data[Op. No]
VAR T1=FILTER(Data,Data[Description]=EARLIER(Data[Description])&&Data[Date]=EARLIER(Data[Date])&&Data[Line]=EARLIER(Data[Line])&&Data[File No]=EARLIER(Data[File No]))
VAR CurAcc = SUMX(FILTER(T1,Data[Op. No]=CurOp),[Q'ty])
VAR PreviousAcc = SUMX(FILTER(T1,Data[Op. No]=CurOp-1),[Q'ty])
RETURN
if(CurOp=1,0,
(CurAcc-PreviousAcc)/COUNTAX(FILTER(T1,Data[Op. No]=CurOp),[Q'ty]))

[V-pazhen-msft]

@thongnguyen2414 

I created today WIP and previousdate WIP columns with a bit simplified logic, please check the dax for detail.

Today WIP =
var curOPno = [Q'ty]
var PreOpno = CALCULATE(SUM([Q'ty]),FILTER('Data',
'Data'[Description]=EARLIER('Data'[Description])
&&'Data'[Date]=EARLIER('Data'[Date])
&&'Data'[Line]=EARLIER('Data'[Line])
&&'Data'[File No]=EARLIER('Data'[File No])
&&[Op. No]=EARLIER('Data'[Op. No])-1))
Return IF([Op. No]=1,0,curOPno-PreOpno)

Previous WIP =
CALCULATE(MAX([Today WIP]),FILTER('Data',
'Data'[Description]=EARLIER('Data'[Description])
&&'Data'[Date]=EARLIER('Data'[Date])-1
&&'Data'[Line]=EARLIER('Data'[Line])
&&'Data'[File No]=EARLIER('Data'[File No])
&&[Op. No]=EARLIER('Data'[Op. No])))

'

 Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[thongnguyen2414]

Dear @V-pazhen-msft 

Thank you so much for your advice. 

[thongnguyen2414]

Hello

Could any one kindly help me to figure out how to calculate for the 'previous date Operation WIP' & 'ACCUM. Operation WIP' ? I tried but still not successfully.

[thongnguyen2414]

I tried to calcualte the Previous date WIP by the below:

Previous WIP = 
VAR CurOp = Data[Op. No]
VAR T1=FILTER(Data,Data[Description]=EARLIER(Data[Description])&&Data[Date]=EARLIER(Data[Date])-1 &&Data[Line]=EARLIER(Data[Line])&&Data[File No]=EARLIER(Data[File No]))
VAR CurAcc = SUMX(FILTER(T1,Data[Op. No]=CurOp),[Q'ty])
VAR PreviousAcc = SUMX(FILTER(T1,Data[Op. No]=CurOp-1),[Q'ty])
RETURN
if(CurOp=1,0,
(CurAcc-PreviousAcc)/COUNTAX(FILTER(T1,Data[Op. No]=CurOp),[Q'ty]))

On the June - 02 , the chart is showing correct, but not for Op. No 4 & 5

And on the June-3, calculation became wrong properly 😞

[thongnguyen2414]

Hi

Can you please kindly advise if there is something wrong with the Variable calculation for Previuos date?

[Vera_33 · Accepted solution]

Hi @thongnguyen2414 

My bad...mistakenly clicked the button to accept the solution...

I modified the one from @V-pazhen-msft a little bit

Previous WIP1 = 
CALCULATE(SUM([Today WIP]),FILTER('Data',
'Data'[Description]=EARLIER('Data'[Description])
&&'Data'[Date]<EARLIER('Data'[Date])
&&'Data'[Line]=EARLIER('Data'[Line])
&&'Data'[File No]=EARLIER('Data'[File No])
&&[Op. No]=EARLIER('Data'[Op. No])))

@V-pazhen-msft  how can I undo Accept the solution?

[thongnguyen2414]

Hi @Vera_33 

1. I am always appreciated for your kindly help. 😍

2. Actually, I shorted my data by operation to get it shorter because the original one is too long as 1 operation has many people working for. I am sorry about not showing enough data.

3. Because of my mistake, when running fomular, it counted x2, x3, x4 ... 

Therefore, I edited the fomular by deviding to countax for removing duplicates value.

The fomular seems to be loong, doesn't it ? 😊

Is there another way to make it shorter?

[Vera_33]

Hi @thongnguyen2414 

It does not matter whether the code is long or not, but it does matter whether it has side effect...I am not a fan of EARLIER, so always use Variable...you can go to DAX Formatter to format your DAX code, practising with different formulas and you will find a better way

DAX Formatter by SQLBI