Sort as per date column in measure

[kamalbandam]

Hi

I have a table as following:

Date	No of Days	Type
12-Nov-2020	12	A
30-Jan-2021	5	B
4-Apr-2020	8	C
5-May-2021	2	S
6-Aug-2021	22	A

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

[v-angzheng-msft · Accepted solution]

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.

[NidhiBhusari]

Hi @kamalbandam ,

Try using the DAX in screenshot.

[kamalbandam]

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:

Date	No of Days	Type	Name
12-Nov-2020	12	A	Mark
30-Jan-2021	5	B	Mark
4-Apr-2020	8	C	Mark
5-May-2021	2	S	Mark
6-Aug-2021	22	A	Mark
31-Dec-2020	21	C	John
26-Jan-2021	15	S	John
3-Mar-2021	4	A	John

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]

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

[NidhiBhusari]

@kamalbandam I have assigned ID to each row and then created two measures as shown in below attached screenshots. 

[kamalbandam]

but it is static where you keep the index number as you know it but we need to do it dynamically