Identifying Changes in Data Lists month on month

[GJUDGE]

Hi, 

I have a data set of projects for each month of the year that contain a unique project name and reporting period (i.e. whatever month it is). I am trying to find the most straightforward way to highlight the projects that have been added  or removed from one reporting month to the next. 

Each month has its own table in Power BI but I have also appended all months togheter - I'm not sure which way is best to get the answer I'm looking for! I am able to run the except function to compare the Jan table to the Feb table for example, but I assume there is an easy way to do this for a series rather than having to do it for each individual month?

I've provided some illustrative data below and the solution I would like to get to:

Project	Reporting Period
A	Jan-21
B	Jan-21
C	Jan-21
D	Jan-21
A	Feb-21
C	Feb-21
D	Feb-21
E	Feb-21

Would like to produce the following, and if I can drill down on the projects added/ removed that would be great:

Reporting Month	No. of Projects Added	No. of Projects Removed	Net Movement
Feb	+1	-1	0

Thanks,
Gary 

[PaulDBrown]

Thanks for also posting the model. As it is, it's relevant to the problem. 
There are two issues. Firstly, the project dimension table must contain unique values and the relationship should then be one-to-many with  the All months appended table in a single direction. 
Then there is a problem in the second measure. You have

VALUES ( 'All Months Appended' )

and it should be 

VALUES ( 'All Months Appended'[Project] )

[GJUDGE]

Thanks again. I've updated the measure but having issues trying to sort the relationship between the project dimension table and the All Months Appended table. I have ensured that duplicates have been removed from the dimension table but am still getting an error stating that the cardinality selected isn't valid when opting for many to one. There are duplicates in the All Months Appended table (the same project appears in Jan, Feb etc. reporting) so wouldn't want to remove duplicates from here too?

[PaulDBrown]

you only need to remove duplicates from the project dimension table. First, delete the relationship between both tables. Then please post a screenshot of the project dimension table in table view.

Also, how did you create the dimension table?

[GJUDGE]

I've reviewed all projects and identified some case sensitivities that prevented all duplicates being removed. I've corrected this and have been able to create the one to many single relationship. 

The measures still don't seem to be pulling through the correct data though. Added projects appears to bring all the projects for the month and i'm getting nothing for projects removed. I've posted the measures again, just in case you see something else i've missed. 

Number of projects added =

VAR PrevPeriodValues =

CALCULATETABLE (

VALUES ( 'All Months Appended'[Project ] ),

FILTER (

ALL ( 'Period Dimension Table' ),

'Period Dimension Table'[Index]

= MAX ( 'Period Dimension Table'[Index] ) - 1

)

)

VAR CurrValues =

VALUES ('All Months Appended'[Project ])

RETURN

COUNTROWS ( EXCEPT ( CurrValues, PrevPeriodValues ) )

Number of projects Removed =

VAR PrevPeriodValues =

CALCULATETABLE (

VALUES ( 'All Months Appended'[Project ] ),

FILTER (

ALL ( 'Period Dimension Table' ),

'Period Dimension Table'[Index]

= MAX ( 'Period Dimension Table'[Index]) - 1

)

)

VAR CurrValues =

VALUES ( 'All Months Appended'[Project ] )

RETURN

COUNTROWS ( EXCEPT ( PrevPeriodValues, CurrValues ) ) * -1

[PaulDBrown]

Ok. Let's try and debug this. Firstly, make sure the relationships are between corresponding fields. Make sure that the dimension field type is the same as the fact table (they should both be type text). Also check that the index column in the period dimension table is a whole number. Then make sure the period field in the period dimension table is sorted by the index column. 
Check that the fields in the visual are from the dimension tables.
You can also run through each measure step by step by returing each var one at a time to make sure the correct values are actually returned for each VAR. 

If all this doesn't solve the problem, please share  a sample PBIX file hiding confidential data. You can recreate tables in Excel and just post them in your post.

[GJUDGE · Accepted solution]

I was pulling in the incorrect fields for the visual! Works perfectly now! Thank you very much for your patience and support!

[PaulDBrown]

Great that you worked it out!

please mark the relevant post as the solution to the original request. It will help others looking for a similar solution 

[PaulDBrown]

Here is one way.

First create dimension tables for the Project and Period fields (this isnt actually strictly necessary to get the results you need, but it does follow modeling best practices). The model should look like this:

 The period column contains a sort column for sorting purposes and to calculate the previous period. You can add this with a new index column in Power Query or a rank calculated column:

Next create the measures:

Current No. of Projects = DISTINCTCOUNT('DataTable'[Project])

Number of projects added =
VAR PrevPeriodValues =
    CALCULATETABLE (
        VALUES ( 'DataTable'[Project] ),
        FILTER (
            ALL ( 'Reporting Period' ),
            'Reporting Period'[Sort]
                = MAX ( 'Reporting Period'[Sort] ) - 1
        )
    )
VAR CurrValues =
    VALUES ( 'DataTable'[Project] )
RETURN
    COUNTROWS ( EXCEPT ( CurrValues, PrevPeriodValues ) )

Number of projects Removed =
VAR PrevPeriodValues =
    CALCULATETABLE (
        VALUES ( 'DataTable'[Project] ),
        FILTER (
            ALL ( 'Reporting Period' ),
            'Reporting Period'[Sort]
                = MAX ( 'Reporting Period'[Sort] ) - 1
        )
    )
VAR CurrValues =
    VALUES ( 'DataTable'[Project] )
RETURN
    COUNTROWS ( EXCEPT ( PrevPeriodValues, CurrValues ) ) * -1

Net Movement =
IF (
    ISINSCOPE ( 'Dim Project'[DProject] ),
    BLANK (),
    [Number of projects added] + [Number of projects Removed]
)

And finally create the matrix with the fields from the Reporting Period and Dim Project as rows, and add the measures to get (I've filtered out the row for January using the filters on the visual in the filter pane):

You can leave out the [Current No. of projects] of course; I've included it to help see the result by project rows

I've attched a sample PBIX file

[GJUDGE]

Hi Paul, 

I appreciate your help with this. I have tried to recreate as per your instructions but seem to have hit a couple of stumbling blocks  (I can't open your PBIX either as I am running an older version of Power BI:

- the projects added measure simply brings back all current projects and not only the new ones. It does however recognise a month where there are no new projects and the output for this month is blank.

- the projects removed measure gives me an error  "each table argument of except must have the same number of columns". I'm not sure how to correct this!

Any further advice would be appreciated. 

[PaulDBrown]

Can you post both your measures?

[GJUDGE]

Here they are:

Number of projects added =

VAR PrevPeriodValues =

CALCULATETABLE (

VALUES ( 'All Months Appended'[Project ] ),

FILTER (

ALL ( 'Period Dimension Table' ),

'Period Dimension Table'[Index]

= MAX ( 'Period Dimension Table'[Index] ) - 1

)

)

VAR CurrValues =

VALUES ('All Months Appended'[Project ])

RETURN

COUNTROWS ( EXCEPT ( CurrValues, PrevPeriodValues ) )

Number of projects Removed =

VAR PrevPeriodValues =

CALCULATETABLE (

VALUES ( 'All Months Appended' ),

FILTER (

ALL ( 'Period Dimension Table' ),

'Period Dimension Table'[Index]

= MAX ( 'Period Dimension Table'[Index]) - 1

)

)

VAR CurrValues =

VALUES ( 'All Months Appended'[Project ] )

RETURN

COUNTROWS ( EXCEPT ( PrevPeriodValues, CurrValues ) ) * -1

[GJUDGE]

screenshot of my dimension tables too - could my choice of cardinality be affecting the output?