count occurrence since last change

[Anonymous]

Hello,

I need to pick your brains for the below situation I'm having trouble doing in PowerBI... limited experience here...

See below sample data:

I need to count backwards from today's week number (let's say today is week 7) for each project how many weeks has it been at the current (latest) status.

As you can see, records are not always updated weekly, or in a particular order (but I'm thinking that shouldn't matter as long as the measures/conditional columns are solid).

Any thoughts?

Much appreciated!

[sturlaws]

Hi @Anonymous 

I found a solution, based on the data you have provided, you could try. 

If you don't already have it, first create a date table with week numbers. There should be no relationship between the project table and the new date table.

Then create a calculated column like this in the project table:

StatusChangeFlag =
VAR _currentProject =
    CALCULATE ( SELECTEDVALUE ( 'Table'[projectID] ) )
VAR _currentStatus =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Status] ) )
VAR _currentWeek =
    CALCULATE ( SELECTEDVALUE ( 'Table'[week number] ) )
VAR _prevWeekNr =
    CALCULATE (
        MAX ( 'Table'[week number] );
        FILTER (
            ALL ( 'Table' );
            'Table'[projectID] = _currentProject
                && 'Table'[week number] < _currentWeek
        )
    )
VAR _previousStatus =
    CALCULATE (
        VALUES ( 'Table'[Status] );
        FILTER (
            ALL ( 'Table' );
            'Table'[projectID] = _currentProject
                && 'Table'[week number] = _prevWeekNr
        )
    )
RETURN
    IF ( _currentStatus <> _previousStatus; 1; 0 )

The purpose of this column is to identify the rows in the project table where the status changes.

Now create this measure

Weeks since last status change =
VAR _currentWeek =
    SELECTEDVALUE ( dimDate[week number] )
VAR _currentProject =
    SELECTEDVALUE ( 'Table'[projectID] )
VAR _prevWeekWithChange =
    CALCULATE (
        MAX ( 'Table'[week number] );
        FILTER (
            ALL ( 'Table' );
            'Table'[projectID] = _currentProject
                && 'Table'[week number] <= _currentWeek
                && 'Table'[StatusChangeFlag] = 1
        )
    )
RETURN
    _currentWeek - _prevWeekWithChange

Now create a table visual, and add ProjectID, Status and [Weeks since last status change] to the visual. Create a slicer visual, and add weeknumber from the date table(which do not have a relationship the project table). The way the [Weeks since last status change]-measure is written, it will only return a value if there is only one value for 'date table'[week number] in the context, so select a week number, e.g. 7

In order to ensure only the latest status change is shown(with week num equal or less than the selected week), we need one more measure:

maxRowFilter =
VAR _currentWeekNum =
    CALCULATE ( SELECTEDVALUE ( 'dimDate'[week number] ) )
VAR _currentProjectID =
    CALCULATE ( SELECTEDVALUE ( 'Table'[projectID] ) )
VAR _maxProjectIdWeekNum =
    CALCULATE (
        MAX ( 'Table'[week number] );
        FILTER (
            ALL ( 'Table' );
            'Table'[projectID] = _currentProjectID
                && 'Table'[week number] <= _currentWeekNum
                && 'Table'[StatusChangeFlag] = 1
        )
    )
VAR _maxRowFilter =
    FILTER (
        'Table';
        'Table'[projectID] = _currentProjectID
            && 'Table'[week number] = _maxProjectIdWeekNum
    )
RETURN
    COUNTROWS ( _maxRowFilter )

This measure needs to be added to filter pane of the table visual, and set to filter equal to 1.

A bit confusing perhaps? I have uploaded a pbix-file to show you what I have done: pbix 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

Hi @sturlaws 

First of all, many thanks for taking a crack at it!

I've created a DimDate table (unrelated to the project table) which contains the date itself & week numbers as separate columns.

One question on the below function:

 FILTER (
            ALL ( 'Table' );
            'Table'[projectID] = _currentProject
                && 'Table'[week number] < _currentWeek

 'Table' -> reffers to which of the 2 tables Project/date?

If I use the date table, then the following line 'table'[projectID] gets marked up and the formula throws an error. If I do the other way around and use the project table, then the 3rd line 'table'[week number] gets marked up and the formula throws an error.

Also, one thought -> instead of using the outside table for the weeknumber; would it work to replace those references with weeknum(today()) ?

Best regards,

Andrei

[sturlaws]

I should have written 'project' insted of 'Table', sorry about that.

I assumed you had a week column in you project table as well, so if you don't have it, you need to create it to get it to work.

You can change weeknumber to weeknum(today()), as long as you don't want to change the value by filtering it

[Anonymous]

Got it! Just one final error before getting it to work:

IF ( _currentStatus <> _previousStatus, 1, 0 )
A table of multiple values was supplied where a single value was expected

Any ideea what would cause that? 

[sturlaws]

do you have projects with more than 1 status pr week?

[Anonymous]

Unfortunately yes. I'm pulling data from a SharePoint list where I copy the status at each of their save. Because of typos or further updates to a project, it happens that they have to update the status more than once per week.

Any way around that? Like getting the last status from each week, i.e. every Thursday or Friday

[sturlaws · Accepted solution]

Now that is something which would be relevant to mention in your original post 😉

You can create a new column to identify the last status pr week, something like this

LastStatusPrWeekFlag =
VAR _currentProject =
    CALCULATE ( SELECTEDVALUE ( 'Table'[projectID] ) )
VAR _currentWeek =
    CALCULATE ( SELECTEDVALUE ( 'Table'[week number] ) )
VAR _maxStatusDate =
    CALCULATE (
        MAX ( 'Table'[status date] );
        FILTER (
            ALL ( 'Table' );
            'Table'[projectID] = _currentProject
                && 'Table'[week number] < _currentWeek
        )
    )
RETURN
    IF ( 'Table'[status date] = _maxStatusDate; 1; 0 )

where 'Table'[status date] would be the datetime column for each of your updates/saves from the Sharepoint list. 

Now add this flag into the calculation of the [StatusChangeFlag]-column:

VAR _previousStatus =
    CALCULATE (
        VALUES ( 'Table'[Status] );
        FILTER (
            ALL ( 'Table' );
            'Table'[projectID] = _currentProject
                && 'Table'[week number] = _prevWeekNr
		&& 'Table'[LastStatusPrWeekFlag]=1 
        )
    )

Also make a small change in the if-statement of the same column:

IF ( _currentStatus <> _previousStatus && table[LastStatusPrWeekFlag]=1; 1; 0 )

[Anonymous]

Many thanks @sturlaws ! It works!

The only change from the below was that I had to leave the if-statement as originally mentioned by you:

IF ( _currentStatus <> _previousStatus, 1, 0 )

If I use the updated version all my  StatusChangeFlag's remain on 0.