topic Re: Dynamic Ageing in DAX Commands and Tips

Dynamic Ageing

akhtar0507 — Sat, 13 Nov 2021 06:38:54 GMT

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.

Re: Dynamic Ageing

akhtar0507 — Sat, 13 Nov 2021 12:13:00 GMT

PaulDBrown

Re: Dynamic Ageing

PaulDBrown — Sat, 13 Nov 2021 12:46:05 GMT

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

Re: Dynamic Ageing

akhtar0507 — Sat, 13 Nov 2021 13:15:43 GMT

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

Re: Dynamic Ageing

faisalazh — Mon, 17 Jan 2022 03:13:27 GMT

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.