Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to use EARLIER function

HI, all   I need your help about how to use EARLIER function. The detail is below.   <data model>   <What I realize> To calculate sum of shipment by date as below using EARLIER function...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , if you are creating a measure then try like , assuming sum_shipment is a measure

     

    cumulation2 = CALCULATE('Measure_001_shipment'[sum_shipment], filter(allselected('Dimension_002_Calender') , 'Dimension_002_Calender'[Date] >= max('Dimension_002_Calender'[Date])) )

     

    or

     

     

    cumulation2 = CALCULATE('Measure_001_shipment'[sum_shipment], filter(allselected('Dimension_002_Calender') , 'Dimension_002_Calender'[Date] <= max('Dimension_002_Calender'[Date])) )

     

    You can use all in place of allselected

     

    Earlier we will use in case same table operation in a new column

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak 

I really appreciate your kind help again!

 

Earlier we will use in case same table operation in a new column

->I'm afraid I can't understand this. Do you mean that 'EARLIER' function can't be used for measure calculation but only for creating new column ?

 

And I'm sorry to trouble you , but I have one additional question about your ansewer.

I can't understand why ""max('Dimension_002_Calender'[Date]) "" can refer "date" which is visualized (= filtered) altough due to allselected('Dimension_002_Calender') , filte is removed .

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    //Do you mean that 'EARLIER' function can't be used for measure calculation but only for creating new column?

    Although we usually use EARLIER in computed columns, that doesn't mean EARLIER can only be used in computed columns. EARLIER requires two layers of iterators to build the environment required by EARLIER(For example, two levels of row contexts are constructed using the iterative functions ADDCOLUMNS and FILTER.), which is cumbersome.

     

    //I can't understand why "max('Dimension_002_Calender'[Date]) "" can refer to "date" which is visualized (= filtered) although due to allselected('Dimension_002_Calender'), filter is removed.

    The [Date] column has an external filter with only one fixed value per row, and MAX is used to obtain this value. Using MIN has the same effect.

     

    Please read this Blog hope it can help you.

    行上下文嵌套和EARLIER

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Anonymous 

      Dear Gao-san

      I appreciate your support.

      What concerns about 'EARLIER', I will learn more based on your advice

      What concerns about second question, I uderstand partly but I may misunderstand the way 'FILTER' function work.

       

      My understand about

       

      filter(allselected('Dimension_002_Calender') , 'Dimension_002_Calender'[Date] <= max('Dimension_002_Calender'[Date]))

       

      is below

       

      1. Call table from argument 1,which in this case is 'allselected('Dimension_002_Calender')'
      2. By 'ALLSELCTED' function, external filter is removed.
      3. Then, argument 2 is reffered , which in this case is ''Dimension_002_Calender'[Date] <= max('Dimension_002_Calender'[Date])'
      4. At this point, Dimension_002_Calender'[Date] is table which has only one columns , [Date] and its filter is expanded to selected dates
      5. Therefore max('Dimension_002_Calender'[Date])' may be literally max of table Dimension_002_Calender'[Date]

      Pleae correct me because I may misunderstand in what order function works