Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

count occurrence since last change

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 w...
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.