sum of deviation in relation to previous month

[Anonymous]

Hi everyone

I have a problem and I can not solve it.
I have a table like this:

I need to build a graph with a sum of deviation in relation to previous month. To count the deviation I need to consider always the max – min between the current month and the previous month
For example, to February:

And finally, I need to create the chart like this:

I am trying but probably I am doing something wrong-

Does someone help me?

[Fowmy · Accepted solution]

@Anonymous 

Create a Date table as it will help you with the time intelligence functions. Check the attached file.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I suggest having a dim-calendar table like below.

please check the below picture and the sample pbix file's link down below.

Number of Sales Total =

SUM(Sales[No of Sales])

Deviation =

SUMX (

CROSSJOIN ( VALUES ( 'Calendar'[Month & Year] ), VALUES ( Sales[Product] ) ),

IF (

ISBLANK (

CALCULATE ( [Number of Sales Total], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

),

BLANK (),

ABS (

[Number of Sales Total]

- CALCULATE ( [Number of Sales Total], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

)

)

)

https://www.dropbox.com/s/yfcuvl1cle3laln/rick.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I suggest having a dim-calendar table like below.

please check the below picture and the sample pbix file's link down below.

Number of Sales Total =

SUM(Sales[No of Sales])

Deviation =

SUMX (

CROSSJOIN ( VALUES ( 'Calendar'[Month & Year] ), VALUES ( Sales[Product] ) ),

IF (

ISBLANK (

CALCULATE ( [Number of Sales Total], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

),

BLANK (),

ABS (

[Number of Sales Total]

- CALCULATE ( [Number of Sales Total], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

)

)

)

https://www.dropbox.com/s/yfcuvl1cle3laln/rick.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

Thank you so much.

I tried to do your advice but insted of create a measure I am creating a column using dax to add on table the desviation column but your rule doesn´t work.

Could you please explain me why?

Thanks

[Jihwan_Kim]

Hi, @Anonymous 

Thank you for your feedback.

I think you cannot use the formula for creating a column that is for creating a measure.

how does your expected outcome look like?

[Anonymous]

I would like to create a new column with desviation as your measure do. I want to create a columns like this:

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please check the below.

deviation CC =

VAR currentdate = Sales[Date]

VAR currentproduct = Sales[Product]

VAR previousdate =

CALCULATE (

MAX ( Sales[Date] ),

FILTER ( Sales, Sales[Product] = currentproduct && Sales[Date] < currentdate )

)

VAR previousnoofsales =

CALCULATE (

SUM ( Sales[No of Sales] ),

FILTER ( Sales, Sales[Product] = currentproduct && Sales[Date] = previousdate )

)

RETURN

IF (

NOT ISBLANK ( previousnoofsales ),

ABS ( Sales[No of Sales] - previousnoofsales )

)

https://www.dropbox.com/s/yfcuvl1cle3laln/rick.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Fowmy · Accepted solution]

@Anonymous 

Create a Date table as it will help you with the time intelligence functions. Check the attached file.

[selimovd]

Hey @Anonymous ,

you can calculate the value from the last month and subtract that from the current month:

Deviation Last Month = SUM( myTable[No of Sales] ) - CALCULATE( SUM( myTable[No of Sales] ), DATEADD( myTable[Date], -1, MONTH ) )

This should work for your case.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Hello
Thanks, but you resolution doesn´t work.