Identify and display changes in data between month m and m-1

[Anonymous]

Hello,

I have an excel file which contains the different products ordored by a company every month. See the table below :

 I would like to display in May the changes that have occurred on the products ordered compared to April. 

Example :

Or

Can somebody help me do this please?

Best regards,

Rena.

[v-chenwuz-msft]

Hi @Anonymous ,

You can do that by creating two measure, one named new color and another new stock.

New Color =
VAR _c =
    CALCULATE ( MAX ( 'Table'[Color] ), PREVIOUSMONTH ( 'Table'[Month] ) )
RETURN
    IF (
        NOT ( ISBLANK ( _c ) )
            && _c <> MAX ( 'Table'[Color] ),
        MAX ( 'Table'[Color] )
    )


New Stock =
VAR _s =
    CALCULATE ( MAX ( 'Table'[Stock] ), PREVIOUSMONTH ( 'Table'[Month] ) )
RETURN
    IF (
        NOT ( ISBLANK ( _s ) )
            && _s <> MAX ( 'Table'[Stock] ),
        MAX ( 'Table'[Stock] )
    )

Result:

If you want get the result dynamicly by slicer. Try this:

Measure =
VAR _select =
    SELECTEDVALUE ( 'Slicer'[Compare] )
VAR _color =
    CALCULATE ( MAX ( 'Table'[Color] ), PREVIOUSMONTH ( 'Table'[Month] ) )
VAR _stock =
    CALCULATE ( MAX ( 'Table'[Stock] ), PREVIOUSMONTH ( 'Table'[Month] ) )
VAR _switch_result =
    SWITCH ( _select, "Color", MAX ( 'Table'[Color] ), MAX ( 'Table'[Stock] ) )
VAR _map_column =
    SWITCH (
        _select,
        "Color",
            _color <> MAX ( 'Table'[Color] )
                && NOT ( ISBLANK ( _color ) ),
        _stock <> MAX ( 'Table'[Stock] )
            && NOT ( ISBLANK ( _stock ) )
    )
RETURN
    IF ( _map_column, _switch_result )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

Hello,

Thanks for the response but it does not work for me. I have this error : 

It says that column specified in the "PREVIOUSMONTH" function is not date type i don't understand why

[v-chenwuz-msft]

Hi @Anonymous ,

The type of this [Month] should be date. For example, 2022-05, you can change it to 2022-05-01, and format it as "yyyy-mm".

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

My Month Column is already the date type that's why i don't understand the error.

[v-chenwuz-msft]

Hi @Anonymous ,

Please share your pbix file without sensitive data and i will help you to fix it.

Best Regards

Community Support Team _ chenwu zhu

[Anonymous]

Hello,

Here is the link to the .pbix file cause i don't have the option to attach a file here.

https://drive.google.com/file/d/1OpNhaRSYJHVLzFYFwgdz_UgqAkSCFpzt/view?usp=sharing

Best regards,

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Sorry for reply late, one parameter error occurs in your code.

And add one more filter in this expression via ALL('Data-Products').

ColorChanges = 
var _c = CALCULATE(MAX('Data-Products'[Color]),PREVIOUSMONTH('Data-Products'[Month]),ALL('Data-Products'))
return
IF(not(ISBLANK(_c)) && _c<>MAX('Data-Products'[Color]),MAX('Data-Products'[Color]))

I have modefiy your pbix in the attachment.

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

Hello,

Thank you so much.

It works.

Best regards,

[speedramps]

A few people have asked the same question tonight with almost the same data. 

Is this school homework? 😀😀😀