cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thongnguyen2414
Frequent Visitor

Using variable to calculate data of previous date & current date

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:

DateLineFile NoDescriptionOp. NoQ'ty
6/1/2021LINE 5VSCG213604941760
6/1/2021LINE 5VSCG2136049421398
6/1/2021LINE 5VSCG2136049431664
6/1/2021LINE 5VSCG2136049441715
6/1/2021LINE 5VSCG2136049451783
6/1/2021LINE 5VSCG2136049461758
6/1/2021LINE 5VSCG2136049471508
6/1/2021LINE 5VSCG2136049481434
6/1/2021LINE 5VSCG2136049491444
6/2/2021LINE 5VSCG2136049412155
6/2/2021LINE 5VSCG2136049422304
6/2/2021LINE 5VSCG2136049431811
6/2/2021LINE 5VSCG2136049441773
6/2/2021LINE 5VSCG2136049451950
6/2/2021LINE 5VSCG2136049461867
6/2/2021LINE 5VSCG2136049471961
6/2/2021LINE 5VSCG2136049481737
6/2/2021LINE 5VSCG2136049492070
6/3/2021LINE 5VSCG2136049412279
6/3/2021LINE 5VSCG2136049422131
6/3/2021LINE 5VSCG2136049431926
6/3/2021LINE 5VSCG2136049441891
6/3/2021LINE 5VSCG2136049452052
6/3/2021LINE 5VSCG2136049462112
6/3/2021LINE 5VSCG2136049472046
6/3/2021LINE 5VSCG2136049482097
6/3/2021LINE 5VSCG2136049492026

 

 

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]))

 

thongnguyen2414_0-1633927655452.png

 

 

1 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

Vera_33_0-1634603206175.png

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?

View solution in original post

8 REPLIES 8
V-pazhen-msft
Community Support
Community Support

@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])))
'
Vpazhenmsft_0-1634111509003.png

 

 

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

Dear @V-pazhen-msft 

 

Thank you so much for your advice. 

thongnguyen2414
Frequent Visitor

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
Frequent Visitor

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

thongnguyen2414_0-1633928525792.png

thongnguyen2414_1-1633928548814.png

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

 

 

 

Hi

 

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

Hi @thongnguyen2414 

 

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

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

Vera_33_0-1634603206175.png

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?

View solution in original post

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 ... 

thongnguyen2414_0-1634779845233.png

 

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?

thongnguyen2414_1-1634780060549.png

thongnguyen2414_2-1634780100764.png

thongnguyen2414_3-1634780153715.png

 

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors