Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Tracking Count of Rows on dataset

Hi, my requirement is to track the number of rows in my dataset on a weekly basis. 

 

For example:

Date: 09/09 there are 120 rows in the dataset.

Data: 16/09 there are 115 rows in the dataset (as user has removed rows in sharepoint excel datasheet). 

 

I want to show that at current date 16/09 that there has been a reduction of 5 rows in the week totalling 115 rows with a visual showing At 16/09 Total = 115 with arrow down of -5. 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    First, please make sure the data exist in the data source just similar with the below table. 

    Then you can create the measures and make conditional formatting for the visual to get it.

    Difference = 
VAR _curdate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < _curdate )
    )
VAR _prenumbers =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _predate ),
        [Number of rows]
    )
RETURN
    IF ( ISBLANK ( _prenumbers ), BLANK (), [Count] - _prenumbers )
    Conditional formatting = IF ( [Difference] = 0, 0, IF ( [Difference] > 0, 1, -1 ) )

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    First, please make sure the data exist in the data source just similar with the below table. 

    Then you can create the measures and make conditional formatting for the visual to get it.

    Difference = 
VAR _curdate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] < _curdate )
    )
VAR _prenumbers =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _predate ),
        [Number of rows]
    )
RETURN
    IF ( ISBLANK ( _prenumbers ), BLANK (), [Count] - _prenumbers )
    Conditional formatting = IF ( [Difference] = 0, 0, IF ( [Difference] > 0, 1, -1 ) )

    Best Regards