Counting Backwards To a point

[Anonymous]

I've got an issue that I just can't figure out the best dax for.

I am needing to be able to count rows backwards until it no longer fits the pattern( -7 days)

We take a snapshot of a live report once a week. There are many other columns that make this thing a giant mess and the data sources is only reliable on these three columns for what I am needing to do.

If a person is active, they will show up in the report by having a row with data. If they weren't active, they won't have a row. So we have a column with names, a column with dates, adn then the third column is just to flag if that column is the latest one(it is Aug 14 for me right now). We can see that John is not in the current week.

What I am wanting to do is look at those users in the current week, and then how many weeks back they show up. So with Joe, we should see 3, even though he is on the list 5 times. 

[mahoneypat · Accepted solution]

Here is one way to do this, assuming your data are in a table called "Data" (replace with your actual table name).

1.  Make a Calendar table with an expression like this

Calendar = ADDCOLUMNS(CALENDAR(MIN(Data[Report Date]), MAX(Data[Report Date])), "Week", WEEKNUM([Date]))

2. Make a relationship between your Date table and the Report Date column

3. Make this measure

Weeks in a row =
VAR thisweek =
MAX ( 'Calendar'[Week] )
VAR maxweeknothere =
MAXX (
FILTER (
ALL ( 'Calendar'[Week] ),
ISBLANK ( CALCULATE ( COUNTROWS ( data ) ) )
),
'Calendar'[Week]
)
RETURN
thisweek - maxweeknothere

4. Make a table visual with the Name column and the measure above to see result of 3 for Joe

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Mahoneypat, It works with one small caveat. I had to remove all 2019 data, which for this report is just fine. But when we roll to a new year, how do we keep the week number going up rather than resetting back to 1 in the calendar table?

By the way, took me a couple read overs. Making the new date table is exactly what I never, ever would have thought of. Genius, thanks.

[lbendlin]

you want this in DAX? As a calculated column?