Need Help on How to handle two dates filter conditions

[ANV]

Dear Friends,

I have requirement in which there are two dates (for simplicity Date1 and Date2). Requirement is to have a value per month to be aggregated sum of 4 prior months considering both Date1 and Date 2

For example, On Dashboard when users select a Date1 = Dec'23 then they should get a value for Dec 23 to be sum of previous 4 months i.e., {Aug'23 to Nov'23} where both Date1 and Date2 coincide. for example here in this scenario we should get sum of values from Date2 to be oct'23 and nov'23 = 37

Date1	Date2	Value
Apr-23	Jun-23	1
May-23	Jul-23	6
Jun-23	Aug-23	19
Jul-23	Sep-23	15
Aug-23	Oct-23	18
Sep-23	Nov-23	19
Oct-23	Dec-23	7
Nov-23	Jan-24	2
Dec-23	Feb-24	5

Kindly help on this dear friends!

[Dataintellinalytics · Accepted solution]

Try using Intersection in Set Analysis. Select MonthYearDate1 = Dec'23

Sum({<MonthYearDate1=, Date1 = {"$(='>='&MonthStart(Date1,-4)&'<='&MonthEnd(Date1,-1))"} > *
<MonthYearDate1=, Date2 = {"$(='>='&MonthStart(Date1,-4)&'<='&MonthEnd(Date1,-1))"} > } Value)

Dataset used:

Data:
Load * ,Monthname(Date1) as MonthYearDate1,Monthname(Date2) as MonthYearDate2 ;

Load * Inline [
Date1,Date2,Value
01/04/23,01/06/23,1
01/05/23,01/07/23,6
01/06/23,01/08/23,19
01/07/23,01/09/23,15
01/08/23,01/10/23,18
01/09/23,01/11/23,19
01/10/23,01/12/23,7
01/11/23,01/01/24,2
01/12/23,01/02/24,5
];

[ANV]

Thank you so much for responding to my query. It worked 🙂