Forum Discussion

thongnguyen2414's avatar
thongnguyen2414
Frequent Visitor
4 years ago
Solved

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 Anonymous  ^^, 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]))

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi thongnguyen2414 

     

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

    I modified the one from Anonymous 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])))

    Anonymous  how can I undo Accept the solution?

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    thongnguyen2414
    Frequent Visitor
    4 years ago

    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's avatar
      thongnguyen2414
      Frequent Visitor
      4 years ago

      Hi

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi thongnguyen2414 

         

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

        I modified the one from Anonymous 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])))

        Anonymous  how can I undo Accept the solution?

  • thongnguyen2414's avatar
    thongnguyen2414
    Frequent Visitor
    4 years ago

    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.