Dynamic Ageing

[akhtar0507]

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.

[faisalazh]

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.

-

[PaulDBrown]

Can you provide sample data or a mock-up PBIX file?

[akhtar0507]

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

[akhtar0507]

@PaulDBrown