Index on DAX if formula to report properly graphs

[Knelio]

Hi all,

I've the following formula:

Expiration Ageing =

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<0,"Expired",

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=30,"Expires in 30 days",

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=90,"Expires in 3 monhts",

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=180,"Expires in 6 months",

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=270,"Expires in 9 months",

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=360,"Expires in 12 months", "Expires over 12 months"))))))

However on my graphs the sorting is not working out - in Power Editor I've this done fine, however in this data, it's only Direct Query, hence can be only DAX. Anyway to have the index added to the above formula or needs to have an additional formula to use the sorting?

Appreciate your support on this.

Thanks,

[Ritaf1983 · Accepted solution]

Hi @Knelio 
You can add a calculated column based on Expiration Ageing by ranking it like:

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<0,1,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=30,2,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=90,3,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=180,4,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=270,5,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=360,5))))))

and then sort the Expiration Ageing by this column

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

[Ritaf1983 · Accepted solution]

@Knelio 
Yep, syntax problem : []'

[Ritaf1983 · Accepted solution]

Hi @Knelio 
You can add a calculated column based on Expiration Ageing by ranking it like:

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<0,1,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=30,2,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=90,3,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=180,4,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=270,5,

if(DATEDIFF(TODAY(),'Table'[EXPIRATION_DATE], DAY)<=360,5))))))

and then sort the Expiration Ageing by this column

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

[Knelio]

Thanks Rita, I'm having an error from the 4th line - it seems like it is not accepting the whole formula, only till this moment 180,4. If I close here, it will be ok. What can be the issue now? Thanks in advance!

[Knelio]

Thanks a lot! Works perfectly now!

[Ritaf1983]

@Knelio glad to help 🙂