Forum Discussion
Solved
Formulae for repeat usage across rows
Hi, I need to find users who have been present in the data in last 3 month and twice in last three month (incl. current month) like shown in the examples below respectively. Column 1 and 2 are in...
vibhoryadav23 OK, this should do it. PBIX is attached below signature.
Column = VAR __User = [Users] VAR __Period = [Period] VAR __PeriodDate = DATE(LEFT(__Period,4), RIGHT(__Period, 2), 1) VAR __EndDate = EOMONTH(__PeriodDate, -2) VAR __EndDateNum = YEAR(__EndDate) * 100 + MONTH(__EndDate) VAR __Count = COUNTROWS( FILTER( 'Table2', [Users] = __User && [Period] <= __Period && [Period] >= __EndDateNum ) ) VAR __Result = IF( __Count >= 3, 1, 0 ) RETURN __Result Column 2 = VAR __User = [Users] VAR __Period = [Period] VAR __PeriodDate = DATE(LEFT(__Period,4), RIGHT(__Period, 2), 1) VAR __EndDate = EOMONTH(__PeriodDate, -2) VAR __EndDateNum = YEAR(__EndDate) * 100 + MONTH(__EndDate) VAR __Count = COUNTROWS( FILTER( 'Table2', [Users] = __User && [Period] <= __Period && [Period] >= __EndDateNum ) ) VAR __Result = IF( __Count >= 2, 1, 0 ) RETURN __Result
These are the result from 2nd set. Mostly works with some exceotions. I believe it doesnt work when there is change in year. So from 202401 and 202312, it wont be able to take this as a previous month with simple subtraction.Can we fix these as well?
vibhoryadav23 Oh, it's the year changes. I had actually wondered about that originally but the original data didn't include it. Hold please.