Status Ageing (Days) based on Single Date Column

[moh2_sid]

I have a Table with several columns (ID, Name, Company,  Status, Date) which gets updated every Friday.  I am trying to build a Measure to calculate the no of the days the 'Name' has been Invalid (Status ='0') and As the data get updated every week I need to show it for current week (Max Date). If for current week and Status =0 then calculate no of days it has been Invalid for.

For E.g.: For Name :ABC, As the Status is '0' for Current Week (max Date), then Calculate the no of days it has been Inactive for i.e. (No of Days between ( 8/9/2024 and 6/7/24).

Based on the No of days, I would like to build buckets and Show it as dimension.

My Trail:

 Date(Min(Aggr(min(Date),NAME, Date)))  - when I tried this expression in a text box its giving me the expected value i.e  '6/7/24'
 

But when I am selecting max date (8/9/2024) for showing current week values it's changing to '8/9/2024'.

Please let me know if further explanation is required, Thanks a lot for your help

@MayilVahanan @DavidFoster1 

[Padma123]

Hi ,

In script,

load Max(week) as maxweek Resident table1;

let vmax=Peek('maxweek');

in measure use this expression

if(week=$(vmax) and status=0,count({<date={"<=$(=max(date))"}>}date),0)