How to compute Aging Day to 1 to 30 Day 31 to 60 Day ?

[Paulyeo11]

Hi All

My PBI File :-

Paul

https://www.dropbox.com/s/vadfrj5u5rxcypv/AR_ISDN%20v009%20trail.pbix?dl=0

[jaideepnema · Accepted solution]

@Paulyeo11  use the following DAX

Agein Bucket = SWITCH(TRUE(),

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

AND(Query1[AGING]>30,Query1[AGING]<=60),"31-60 Days",

AND(Query1[AGING]>60,Query1[AGING]<=90),"61-90 Days",

AND(Query1[AGING]>90,Query1[AGING]<=120),"91-120 Days",

AND(Query1[AGING]>120,Query1[AGING]<=150),"121-150 Days",

AND(Query1[AGING]>150,Query1[AGING]<=180),"151-180 Days",BLANK())

Here is the required result

Appreciate a Kudos if this resolves your Query !!

[v-robertq-msft · Accepted solution]

Hi, @Paulyeo11 

According to your description, you want to make the value of measure[AGING] displays Split date counts, you can try this measure to replace your current measure:

AGING =

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

return

SWITCH(

    TRUE(),

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

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

    "Over 60 day"

)

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[Paulyeo11]

Hi Robert 

your script very smart , it will sort by itself.

Paul

[Paulyeo11]

Hi Robort

Thank you very much

Paul

[jaideepnema · Accepted solution]

@Paulyeo11  use the following DAX

Agein Bucket = SWITCH(TRUE(),

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

AND(Query1[AGING]>30,Query1[AGING]<=60),"31-60 Days",

AND(Query1[AGING]>60,Query1[AGING]<=90),"61-90 Days",

AND(Query1[AGING]>90,Query1[AGING]<=120),"91-120 Days",

AND(Query1[AGING]>120,Query1[AGING]<=150),"121-150 Days",

AND(Query1[AGING]>150,Query1[AGING]<=180),"151-180 Days",BLANK())

Here is the required result

Appreciate a Kudos if this resolves your Query !!

[Paulyeo11]

Hi All 

Can some one help me how to make X axiz can sort in sequences ?

[Paulyeo11]

Hi Sir

This is beyond my expectation. This is better approach . ( my approach no goos , i create many field. )

Thank you 

Paul