topic how to exclude manager registered 3 months ago from RANKX in DAX Commands and Tips

how to exclude manager registered 3 months ago from RANKX

kristina-brooly — Fri, 25 Aug 2023 11:02:23 GMT

I have 2 tables

Dim_manager

Manager_id

Name

Start_work

Fact_sales

manager_id

sales

date

I made a measure

sales rank =RANKX (ALLSELECTED ('Dim_manager'),CALCULATE( [growth sales]))

Growth sales is a measures here

growth sales = [PreviousMonthSales]-[BeforePreviousMonthSales]

How to exclude managers registered earlier then 3 mpnths ago (start_work) from the sales rank?

Re: how to exclude manager registered 3 months ago from RANKX

v-zhangti — Mon, 28 Aug 2023 05:29:37 GMT

Hi, kristina-brooly

You can try the following methods.

sales rank = Var _Rank=RANKX (FILTER(ALLSELECTED ('Dim_manager'),[Start_work].[MonthNo]>=MONTH(TODAY())-3),CALCULATE([growth sales]),,DESC) Return IF(MAX(Dim_manager[Start_work].[MonthNo])>=MONTH(TODAY())-3,_Rank,BLANK())

If this does not solve your problem, please provide example data.

Best Regards,

Community Support Team _Charlotte

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