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 manag...
ADDCOLUMNS()
ageing AP & AR
DAX
Dynamic age column
faisalazh's avatar
faisalazh
Icon for Advocate II rankAdvocate 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.

 

-