Forum Discussion

akhtar0507's avatar
akhtar0507
Frequent Visitor
4 years ago

Dynamic Ageing

Hello,
I am actually working on an AR ageing report.

My data model is as follows: I have a AR transaction table with the amount, with a Date filed related to a calendar table.

So far, I have managed to get the ageing as at today. I have added a calculated column, to calculate the age of the transaction and have used this measure to make it work:

Ageing AR Amount = CALCULATE(
PostAR[AR Net],
FILTER( DISTINCT(PostAR[Ageing]),
COUNTROWS(FILTER('Ageing Group', PostAR[Ageing] >= 'Ageing Group'[Min] &&
PostAR[Ageing] <= 'Ageing Group'[Max]
)
)))
 
Ageing group table and ageing is as follows:
 
 
So far so good. However, I need to have the calculated column age as dynamic, so that the ageing report is as at a selected date by the user.
I have tried adding the ADDCOLUMNS to caluclated the age based on the date selected, but cannot figured it out.
 
ADDCOLUMNS()
ageing AP & AR
DAX
Dynamic age column

4 Replies

    • akhtar0507's avatar
      akhtar0507
      Frequent Visitor
      4 years ago

      Please find link below of model I am using :
      AgeAR 
      Anticipated thanks.

  • faisalazh's avatar
    faisalazh
    Advocate II
    4 years ago

    Hi Akhtar.

    i think it's not possible to have a dynamic column that respond to a slicer.

    but if you want to have calculated column.

    then you can create a crossjoin calculated table that consist a calendar and age group.
    something like this formula:

    Summary AR = 
var tblCalendar = SELECTCOLUMNS('Calendar', "Date", 'Calendar'[Date])
var tblAgeingGroup = SELECTCOLUMNS('Ageing Group', "Age Group", 'Ageing Group'[Age Group])
var tblClient = SELECTCOLUMNS(Client, "DCLink", Client[DCLink])
return CROSSJOIN(tblCalendar,tblAgeingGroup,tblClient)

    then you can add calculated column for each reporting date, age group and client either to count number of transactions and amount.

    finally, create a relationship between your calendar table with new summary AR table.

     

    -