Comparing Datasets

[deanbland]

Hi, 

I am wanting to compare this month's entire dataset to last months entire dataset to see if anything has changed. Is there a way to create a calculated column in the 'current months' report that assigns a 1 next to any row which is different compared to last months? 

PBIX file with example data can be found here 

https://drive.google.com/file/d/155WoUbjHNJYF456Wl1strfMdcXAv9mHl/view?usp=sharing

[v-lionel-msft]

Hi @deanbland ,

Try to do like this.

1. Create a relationship.
(If you need to compare each row of two tables, then the two tables need to contain a unique identification column (primary key, foreign key)).

2. Create a calculated column in 'Current Month' table.

Column = 
IF(
    [Account Name] = RELATED('Previous Month'[Account Name]) &&
    [Area Unit] = RELATED('Previous Month'[Area Unit]) &&
    [Assessment completed date] = RELATED('Previous Month'[Assessment completed date]) &&
    [Bid fully compliant with client requirements. If not, the variant approach has been approved at DCR] = RELATED('Previous Month'[Bid fully compliant with client requirements. If not, the variant approach has been approved at DCR]) &&
    [Budget Requested] = RELATED('Previous Month'[Budget Requested]) &&
    [Close Date] = RELATED('Previous Month'[Close Date]) &&
    [Date Review Held] = RELATED('Previous Month'[Date Review Held]) &&
    [Delivery Sign-off (DCR1) has been approved] = RELATED('Previous Month'[Delivery Sign-off (DCR1) has been approved]) &&
    [Engagement Complexity Assessment] = RELATED('Previous Month'[Engagement Complexity Assessment]) &&
    [Engagement Complexity Assessment completed and recommended level of Surveillance has been applied] = RELATED('Previous Month'[Engagement Complexity Assessment completed and recommended level of Surveillance has been applied]) &&
    [Export Date] = RELATED('Previous Month'[Export Date]) && 
    [Form Created Date] = RELATED('Previous Month'[Export Date]) &&
    [Form Id] = RELATED( 'Previous Month'[Form Id] ) &&
    [Form Owner] = RELATED('Previous Month'[Form Owner]) &&
    [Form Type] = RELATED('Previous Month'[Form Type])&&
    ...
    0,1
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@deanbland , You can create a new table like

except(calculateTable(table, filter(Table, eomonth(Table[Date],0) = eomonth(today(),0))),calculateTable(table, filter(Table, eomonth(Table[Date],0) = eomonth(today(),-1))))

https://docs.microsoft.com/en-us/dax/except-function-dax