May i know how to sort the Aging 1- 30 come first

[Paulyeo11]

Hi All

[jaideepnema · Accepted solution]

@Paulyeo11 create a calculated column as shown below

here is the code

Ageing Sort = SWITCH(TRUE(),

AND(Query1[AGING]>0,Query1[AGING]<=30),1,

AND(Query1[AGING]>30,Query1[AGING]<=60),2,

AND(Query1[AGING]>60,Query1[AGING]<=90),3,

AND(Query1[AGING]>90,Query1[AGING]<=120),4,

AND(Query1[AGING]>120,Query1[AGING]<=150),5,

AND(Query1[AGING]>150,Query1[AGING]<=180),6,BLANK())

this wil sort the column and you will get the required output as shown below

Appreciate a Kudos if this resolved your query !!

[NamishB]

Hi @Paulyeo11 

Create a new table with age or Days in Asceding to descending order and have an index from 1 

In Power Query > you can create a table from existing table. (right click, Duplicate and remove other columns except Days/Age) and add column Index from 1

Join this new Table with your existing table and in your graph use this new table index in tooltip to sort as ascending/Desc.

Hope this helps.

Cheers,

-Namish B

[amitchandak]

@Paulyeo11 , Try a new column like

Aging 1 = [Aging]

Aging Sort = switch( true() ,
[Aging] ="1- 30" , 1 ,
[Aging] ="30-60" , 2 ,
// keep on adding
,8
)

and sort Aging 1 on Aging  sort and use that in visual

[Paulyeo11]

Hi Amit

I get Aging Sort error :-

[amitchandak]

@Paulyeo11 , I have given two new columns, not one. The first I create to avoid circular dependencies

Aging 1 = [Aging]

Aging Sort = switch( true() ,
[Aging] ="1- 30" , 1 ,
[Aging] ="30-60" , 2 ,
// keep on adding
,8
)

Aging sort will become sort column of aging 1 and you will use aging 1 in visual

[Paulyeo11]

Hi Amit

Still have error :-

[Paulyeo11]

Hi Amit

Below script dont need to sort  , case closed:- 

AGING =

var _datediff=Datediff([AR Invoice Date],today(),day)

return

SWITCH(

TRUE(),

_datediff<=30,"1 to 30 Day",

_datediff<=30,"31 to 60 Day",

_datediff<=61,"61 to 90 Day",

_datediff<=91,"91 to 120 Day",

_datediff<=120,"121 to 150 Day",

_datediff<=151,"151 to 180 Day",

"Over 181 day"

)