cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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. 

 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Counting Backwards To a point

you want this in DAX? As a calculated column?

Highlighted
Super User III
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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

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.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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