Forum Discussion

kamalbandam's avatar
kamalbandam
Helper III
4 years ago
Solved

Sort as per date column in measure

Hi

 

I have a table as following:

DateNo of DaysType

12-Nov-2020

12A
30-Jan-20215B
4-Apr-20208C

5-May-2021

2S
6-Aug-202122A

 

 

Now, I need to sort the date column and calculate the sum of days before Type S from Type column. I have tried but I am not getting how to write a DAX. Any h

Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, kamalbandam 

    Try to create a measure like this:

    Measure = 
var _date_S=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Type]="S"))
var _sum=CALCULATE(SUM('Table'[No of Days]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Date]<_date_S&&'Table'[Date]<=MAX('Table'[Date])))

return _sum

     

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

    • kamalbandam's avatar
      kamalbandam
      Helper III
      4 years ago

      Hi NidhiBhusari 

      Thanks for answering but I have some other rows and  also where I need to take for a particular record of a person as shown below:

       

      DateNo of DaysTypeName

      12-Nov-2020

      		12AMark
      30-Jan-20215BMark
      4-Apr-20208CMark

      5-May-2021

      		2SMark
      6-Aug-202122AMark
      31-Dec-202021CJohn
      26-Jan-202115SJohn
      3-Mar-20214AJohn

       

      Now, I need to get the result as Mark - 25 No of days & John - 21 No of days.

      Sorry for the incomplete info.

      Thanks in Advance

      • NidhiBhusari's avatar
        NidhiBhusari
        Helper IV
        4 years ago

        kamalbandam Do you still want to use this condition - Now, I need to sort the date column and calculate the sum of days before Type S from Type column. I have tried but I am not getting how to write a DAX.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    Hi, kamalbandam 

    Try to create a measure like this:

    Measure = 
var _date_S=CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Type]="S"))
var _sum=CALCULATE(SUM('Table'[No of Days]),FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])&&'Table'[Date]<_date_S&&'Table'[Date]<=MAX('Table'[Date])))

return _sum

     

    Result:

    Please refer to the attachment below for details. Hope this helps.

     

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.