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 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
Amazing. This works.
Btw I was able to solve it by ranking the period column and using it in your formula instead of period, but your new solution is even better.
Thanks Greg_Deckler