Max Date if Max Date is over one month month ago

[DonutKing]

Hi all

I'm wanting to create a calculated dimension that returns data where the most recent submitted date in a field is over a month ago.

Apologies in advance, as I realise that this is very basic, but I'm struggling to get the data I'm expecting to see

[Vegar]

I did not completely understand, please elaborate. Maybe you could give us an example with input and desired output?

/Vegar

[DonutKing]

Thanks Vegar

I have list of user inputted dates that reflect the date a task was completed. Ideally there would be task completed in every month, but this doesn't always occur.

I’m trying to restrict the table to return only instances where, for each Case, a task hasn’t been completed within the past 31 days.

So, in the below example, only ‘Case Name -3’, ‘Case Name – 5’ and’ Case Name – 7’ should be returned if the data is being run at 28 April.

Many thanks

Case ID	Case Title	Submission Date	Days from Today (28 April)
ID - 1	Case Name - 1	10/4/2020	0.58
ID - 1	Case Name - 1	30/1/2020	2.90
ID - 2	Case Name - 2	30/3/2020	0.93
ID - 2	Case Name - 2	31/3/2020	0.90
ID - 3	Case Name - 3	28/2/2020	1.95
ID - 4	Case Name - 4	31/3/2020	0.90
ID - 5	Case Name - 5	31/1/2020	2.88
ID - 6	Case Name - 6	30/3/2020	0.94
ID - 7	Case Name - 7	4/2/2020	2.74

[Vegar]

Try something like this.

CaseData:
LOAD * Inline [
Case ID, Case Title, Submission Date, Days from Today (28 April)
ID - 1, Case Name - 1, 10/4/2020, 0.58
ID - 1, Case Name - 1, 30/1/2020, 2.90
ID - 2, Case Name - 2, 30/3/2020, 0.93
ID - 2, Case Name - 2, 31/3/2020, 0.90
ID - 3, Case Name - 3, 28/2/2020, 1.95
ID - 4, Case Name - 4, 31/3/2020, 0.90
ID - 5, Case Name - 5, 31/1/2020, 2.88
ID - 6, Case Name - 6, 30/3/2020, 0.94
ID - 7, Case Name - 7, 4/2/2020, 2.74
] ;

NotComplete:
LOAD 
	[Case ID],  
	[Not completed Case]
WHERE 
	MaxDate < AddMonths(today(),-1);
LOAD 
	[Case ID],
	[Case Title] as [Not completed Case],
	max([Submission Date]) as MaxDate
Resident CaseData
group by [Case ID],[Case Title]
;

[DonutKing]

Many thanks Vegar