cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Counting Backwards To a point

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III

## Re: Counting Backwards To a point

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

Proud to be a Super User!

3 REPLIES 3
Highlighted
Super User II

## Re: Counting Backwards To a point

you want this in DAX? As a calculated column?

Highlighted
Super User III

## Re: Counting Backwards To a point

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

Proud to be a Super User!

Highlighted
Frequent Visitor

## Re: Counting Backwards To a point

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors