Forum Discussion
Formulae for repeat usage across rows
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
Greg_Deckler I took column 1 form your first set and column 2 from second. Your solution mostly works well except when the months are missing in between, it doesnt take them into account. That's the main problem I am trying to solve. Example: I have combined year and month to form 'Period' as the data can back years in time. If you observe first three rows, there are some months missing for the user but its still being counted in Column 1. For column 2, in second row it should be 1 as its present in 202401 and 202311 (two of last 3 months)
Thanks for the effort btw. Much appriciated!
I have added an example of a user in the original post for more clarity.
vibhoryadav23 That was the purpose of the second set of example column formulas. Those *should* work in the case you are describing. You would want to use column1 and column2 from the second set as these filter the number of rows that are looked back upon so if there are missing months then you *should* get the correct results.
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 __ResultAmazing. 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
vibhoryadav23 Oh, it's the year changes. I had actually wondered about that originally but the original data didn't include it. Hold please.