Count on condition from multiple sorting condition

[Anonymous]

Hi,

I'm trying to create a new custom column for my table which it's gonna count the sort order from other collumns on some condition:

1. it's should be on the same date

2. it should be sorted on it's value on descending order

this is the image for what I want to do:

date_column      |       value_column     |          my_custom_column     |

2019/06/14        |                 8               |                         3                    |

2019/06/14        |                 9               |                         2                    |

2019/06/14        |                10              |                         1                    |

2019/06/15        |                 7               |                         2                    |

2019/06/15        |                 8               |                         1                    |

I've tried this DAX code :

CALCULATE(COUNT('my_table'[value_column]),FILTER('my_table',AND([date_column]=MAX([date_column]),[value_column]<MAX([value_column]))))

but it's not returning the result that I want.

I wondering if there's someone who has a better idea for this.

regards,

[Ashish_Mathur]

Hi,

This calculated column formula works

=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[date_column]=EARLIER(Data[date_column])&&Data[value_column]>=EARLIER(Data[value_column])))

Hope this helps.

[Anonymous · Accepted solution]

hi @Ashish_Mathur ,

thank you very much for your help.

it's close from what I'm looking for, but the problem is, with your code rows with date earlier than 2019/06/14 would also be counted.

I need rows to be counted only on the same day

[Ashish_Mathur]

Hi,

Your formula is not the same as mine.  You are missing the EARLIER() before the &&

[Anonymous]

Hi @Ashish_Mathur 

sorry for that.

it works perfectly now!

thank you very much, really appriciate it!

regards,

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.