Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
GJUDGE
Helper II
Helper II

Identifying Changes in Data Lists month on month

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:

 

ProjectReporting Period

A

Jan-21
BJan-21
CJan-21
DJan-21
AFeb-21
CFeb-21
DFeb-21
EFeb-21

 

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

 

Reporting MonthNo. of Projects AddedNo. of Projects RemovedNet Movement
Feb+1-10

 

Thanks,
Gary 

1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
PaulDBrown
Community Champion
Community Champion

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] )

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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?

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?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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. 

 

GJUDGE_0-1631303335506.png

 

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. 

 

GJUDGE_1-1631303403665.png

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

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.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

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:

Model.JPG

 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:

period.JPGNext 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):

DiffProjectsPeriod1.gif

 

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

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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. 

 

Can you post both your measures?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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

 

GJUDGE_0-1631295181703.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.