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

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

      Hi Anonymous 

       

      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?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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