Ageing in number of days

[TS08]

Hi, can anyone help me calculate ageing in days with conditions. I tried below formula but its not giving me desired result.

If([Status]='Commissioned', Today()-[Action Date])

[BrunPierre · Accepted solution]

Maybe like this at the back end

IF(Match(Status,'Commissioned'),Age(now(),[Action Date])) as [Action Age]

[MarcoWedel]

what is your desired result?

[morgankejerhag · Accepted solution]

Are doing this calculation in a load statement in the script or are you doing in it an expression? If there several data rows for each dimension value you also need to do some aggregation like avg(if(...)). In this case it is also better to use the filter in set analysis avg({<Status={Commissioned}>} today()-[Action Date]).

This also assumes that Action Date is a true date field and not a string looking like a date.

[TS08 · Accepted solution]

Thanks, I tried this and it worked 🙂

IF(Match(Status,'Commissioned'),Age(now(),[Action Date])) as [Action Age]

Thanks once again!

[TS08]

This above solution worked for me but now I am stuck the measure I created to calcualte Ageing is not working in sync with FinMonth (Date Dimention).

I selected one Fiscal Year in Filters (April'22-March'23) however now after  applying this expression I am getting records beyong this period bcz the ageing logic is not interacting with "Fiscal Year".

Please note: the model I am using to devlope these expressions on is interactive with Date Dimetnion throuigh Measures.

Can you help me where this above formula for calcualting "Ageing" moves with Date filter.