Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Aging debtor categorized by invoice date

Hi,

I hope someone can help me with this issue.

 

I want to categorize the following invoices by aging date into following categories

* 0-1 year

*1-2 year

*2-3 year

*3- year

 

My data looks like this:

 

Account customer labelInvoice numberInvoice dateInvoice open amount
A10015-11-16                               10.000
B10121-11-17                               25.000
C10230-11-17                                 5.000
D 10318-12-17                               15.000
E10419-01-18                                 8.500
F10502-02-18                                 8.950
G10605-02-18                               17.500
H 10722-11-19                               23.000
I10823-11-19                               30.000

 

I hope You can help with a Dax code 🙂

Søren

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    You want a column or a measure?

    Column = 
var diff = DATEDIFF('Table'[Invoice date],TODAY(),MONTH)
return
SWITCH(TRUE(),diff<12&&diff>=0,"0-1 year",diff>=12&&diff<24,"1-2 year",diff>=24&&diff<36,"2-3 year","3- year")

Measure = 
var diff = DATEDIFF(selectedvalue('Table'[Invoice date]),TODAY(),MONTH)
return
SWITCH(TRUE(),diff<12&&diff>=0,"0-1 year",diff>=12&&diff<24,"1-2 year",diff>=24&&diff<36,"2-3 year","3- year")

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try both as new columns

    Age = datediff([invoice_date],today(),month)

switch(true(),
[Age] <=12, "0-1 year",
[Age] <=24, "1-2 year",
[Age] <=36, "2-3 year",
">3 year")
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi and thanks for the quick response 🙂 

       

      [invoice date] is not a "Meassure" in my dateset, so I cannot use it in code. Do you have a solution for that? 🙂 

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous ,

     

    You can try it using Power Query:

     

    If you consider it as a solution, please mark as a solution and kudos.

    Ricardo

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    You want a column or a measure?

    Column = 
var diff = DATEDIFF('Table'[Invoice date],TODAY(),MONTH)
return
SWITCH(TRUE(),diff<12&&diff>=0,"0-1 year",diff>=12&&diff<24,"1-2 year",diff>=24&&diff<36,"2-3 year","3- year")

Measure = 
var diff = DATEDIFF(selectedvalue('Table'[Invoice date]),TODAY(),MONTH)
return
SWITCH(TRUE(),diff<12&&diff>=0,"0-1 year",diff>=12&&diff<24,"1-2 year",diff>=24&&diff<36,"2-3 year","3- year")

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

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