How to track changes (text fields) on rows compared to next month?

[Anonymous]

Hi All,

I recently start using PowerBi, as we are running some applications with reporting options, but to get some important stuff out of it seems a no go. I have already searched on video's and this community. But just can't find the solution to it.

The situation is as follows:

We are running a monthly report, with a lot of text fields (seperated by columns) behind a article field index(this is unique).

The text in the columns can change by time as names and or location change. I'm linking to these excel files via de folder option.

I want to capture the lines/rows where some text fields did change or are removed.

And of course a new entry, when an new product is added to our catalog.

Beneath an example:

data extract, Sheet from October

article		BO	AO	DESCRIPTION	RELATION	Month
RGT12		Steve O	YES	RGT12 article description	AAM	october
RGT13		Laura T	YES	RGT13 article description	KAM	october
RGT14		Linda S	YES	RGT14 article description	AAM	october
RGT15		Steve O	YES	RGT15 article description	KLP	october
RGT16		Bert V	YES	RGT16 article description	KLT	october
RGT17		Oliver T	NO	RGT17 article description	KLM	october
RGT18		Kenny B	YES	RGT18 article description	KLM	october
RGT19		Laura T	YES	RGT19 article description	PTR	october
RGT20		Bert V	YES	RGT20 article description	PTR	october
RGT21		Kenny B	YES	RGT21 article description	AAM	october
RGT26		Steve O	NO	RGT26 article description	KLT	october
RGT27		Peter P	YES	RGT27 article description	AAM	october
RGT28		Laura T	YES	RGT28 article description	ASW	october
RGT56		Steve O	YES	RGT56 article description	AAM	october
RGT57		Steve O	NO	RGT57 article description	KLT	october
RGT58		Steve O	YES	RGT58 article description	PTR	october

data extract, Sheet from November.

article		BO	AO	DESCRIPTION	RELATION	Month
RGT13		Laura T	YES	RGT13 article description	KAM	november
RGT14		Linda S	YES	RGT14 new article description	AAM	november
RGT15		Steve O	YES	RGT15 article description	KLP	november
RGT16		Bert V	YES	RGT16 article description	KLT	november
RGT17		Oliver T	YES	RGT17 new article description	KLM	november
RGT18		Benny B	YES	RGT18 article description	KLM	november
RGT19		Laura T	YES	RGT19 article description	new PRT	november
RGT21		Benny B	YES	RGT21 article description	AAM	november
RGT26		Steve O	YES	RGT26 article description	KLT	november
RGT27		Peter P	YES	RGT27 new article description	AAM	november
RGT28		Laura T	YES	RGT28 article description	ASW	november
RGT56		Steve O	YES	RGT56 article description	new AAM	november
RGT57		Steve O	YES	RGT57 article description	KLT	november
RGT58		Steve O	YES	RGT58 article description	PTR	november
RGT59		Laura T	YES	RGT59 article description	new AAM	november
RGT60		Peter T	YES	RGT60 article description	AAM	november
RGT61		Peter T	YES	RGT61 article description	AAM	november
RGT62		Olly R	YES	RGT62 article description	AAM	november

So how can I easily see that e.g. RGT 59, RGT60, RGT61 and RGT62 are new?

and that RGT14 got a new description, and RGT18 changed form Kenny B to Benny B?

Is this possible?

Hope I make sence in my description.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here’s my solution.

1.Create a measure to check the status.

Check =
IF (
    CALCULATE (
        COUNTROWS ( 'october' ) = 0,
        FILTER ( ALL ( 'october' ), 'october'[article] = MAX ( 'november'[article] ))
    ),
    "New",
    IF (
        CALCULATE (
            COUNTROWS ( 'november' ) = 0,
            FILTER (
                ALL ( 'november' ),
                'november'[article] = MAX ( 'october'[article] )
                    && 'november'[AO] = MAX ( 'october'[AO] )
                    && 'november'[BO] = MAX ( 'october'[BO] )
                    && 'november'[DESCRIPTION] = MAX ( 'november'[DESCRIPTION] )
                    && 'november'[RELATION] = MAX ( 'october'[RELATION] )
            )
        ),
        "Change",
        BLANK ()
    )

)

2.Create color measures for conditional formatting.

ColorAO = IF('november'[Check]="Change",IF(MAX('november'[AO])<>MAX('october'[AO]),"Red"))

ColorBO = IF('november'[Check]="Change",IF(MAX('november'[BO])<>MAX('october'[BO]),"Red"))

ColorDES = IF('november'[Check]="Change",IF(MAX('november'[DESCRIPTION])<>MAX('october'[DESCRIPTION]),"Red"))

ColorREL = IF('november'[Check]="Change",IF(MAX('november'[RELATION])<>MAX('october'[RELATION]),"Red"))

3.Select Conditional formatting and turn on Font color, for each column, select the corresponding color measure.

For column AO:

4.Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

@v-yanjiang-msft 

Sorry that I couldnt answer earlier. As I got covid, I'm still strugling a bit with day to day work. I'm still trying to understand what you wrote for these measures. Seems that I'm not there yet.

[v-yanjiang-msft]

Hi @Anonymous ,

First through the measure check, you can get all the article which is different from before or completely new.

For the changed part, in order to highlight the different places, I use conditional formatting, by the color measure, the changed part will be higelighted.

You can download my sample, it can help you to understand.

Best Regards,
Community Support Team _ kalyj

[MFelix]

Hi @Anonymous ,

This depends on how you want to track this changes I have made this trough power query but also trough DAX.

Can you share a little bit more on your model. Is everything in the same table? Are you only tracking changes from previous period or to other periods has well?

[Anonymous]

@MFelix thank you for having a look at this. (couldn't answer earlier because I got sick from covid). But getting better now.

The data sets are really exported excel sheets for every month. So they need to look at the previous month. If the december month will be placed in the folder, It needs to check the november one.... and for January the December month... and so on. The excel sheets are all similar with setup.