Hi , @Anonymous
This is my test data:
ClearCollect(test2, ForAll( testdata.'Roll off Date',{MonthYear: Text(Month(DateValue('Roll off Date')), "[$-en-US]00") & "/" & Text(Year(DateValue('Roll off Date')), "[$-en-US]0000")})
,ForAll( testdata.'Roll in Date',{MonthYear: Text(Month(DateValue('Roll in Date')), "[$-en-US]00") & "/" & Text(Year(DateValue('Roll in Date')), "[$-en-US]0000")})
);
Set(ResultTest ,AddColumns( Distinct(test2,MonthYear) As months , "Roll in Count" , CountRows( Filter(testdata, Text(Month(DateValue('Roll in Date')), "[$-en-US]00") & "/" & Text(Year(DateValue('Roll in Date')), "[$-en-US]0000")=months.Value ) ) ,"Roll off Count", CountRows( Filter(testdata, Text(Month(DateValue('Roll off Date')), "[$-en-US]00") & "/" & Text(Year(DateValue('Roll off Date')), "[$-en-US]0000")=months.Value ) ) ));
The result is as follows:
In your side, you can try to use this code:
ClearCollect(test2, ForAll( Filter(
RMT_Roster,
DateValue('Roll in Date') >= DateValue("9/1/" & Text(Year(Today()) - If(Month(Today()) < 9, 1, 0), "[$-en-US]0000")) &&
DateValue('Roll in Date') <= DateAdd(DateValue("9/1/" & Text(Year(Today()) - If(Month(Today()) < 9, 1, 0), "[$-en-US]0000")), 1, TimeUnit.Years) - 1
).'Roll off Date',{MonthYear: Text(Month(DateValue('Roll off Date')), "[$-en-US]00") & "/" & Text(Year(DateValue('Roll off Date')), "[$-en-US]0000")})
,ForAll( Filter(
RMT_Roster,
DateValue('Roll in Date') >= DateValue("9/1/" & Text(Year(Today()) - If(Month(Today()) < 9, 1, 0), "[$-en-US]0000")) &&
DateValue('Roll in Date') <= DateAdd(DateValue("9/1/" & Text(Year(Today()) - If(Month(Today()) < 9, 1, 0), "[$-en-US]0000")), 1, TimeUnit.Years) - 1
).'Roll in Date',{MonthYear: Text(Month(DateValue('Roll in Date')), "[$-en-US]00") & "/" & Text(Year(DateValue('Roll in Date')), "[$-en-US]0000")})
);
Set(ResultTest ,AddColumns( Distinct(test2,MonthYear) As months , "Roll in Count" , CountRows( Filter(Filter(
RMT_Roster,
DateValue('Roll in Date') >= DateValue("9/1/" & Text(Year(Today()) - If(Month(Today()) < 9, 1, 0), "[$-en-US]0000")) &&
DateValue('Roll in Date') <= DateAdd(DateValue("9/1/" & Text(Year(Today()) - If(Month(Today()) < 9, 1, 0), "[$-en-US]0000")), 1, TimeUnit.Years) - 1
), Text(Month(DateValue('Roll in Date')), "[$-en-US]00") & "/" & Text(Year(DateValue('Roll in Date')), "[$-en-US]0000")=months.Value ) ) ,"Roll off Count", CountRows( Filter(Filter(
RMT_Roster,
DateValue('Roll in Date') >= DateValue("9/1/" & Text(Year(Today()) - If(Month(Today()) < 9, 1, 0), "[$-en-US]0000")) &&
DateValue('Roll in Date') <= DateAdd(DateValue("9/1/" & Text(Year(Today()) - If(Month(Today()) < 9, 1, 0), "[$-en-US]0000")), 1, TimeUnit.Years) - 1
), Text(Month(DateValue('Roll off Date')), "[$-en-US]00") & "/" & Text(Year(DateValue('Roll off Date')), "[$-en-US]0000")=months.Value ) ) ));
If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance!
Best Regards,
Yueyun Zhang
