Difference between the min and Max Value.

[Anonymous]

Hi Team,

Need help 

I have a data as shown below and i have slicer with month.

Number	Value	Month
12	23	Jan
12	34	Feb
12	23	Mar
12	64	Apri
12	54	May
12	76	Jun
12	54	Jul
12	12	Aug
13	98	Jan
13	12	Feb
13	34	Mar
13	45	Apri
13	56	Jun
13	76	Jul
13	67	Aug
13	65	Sep
13	32	Oct

Requirement is to calculate the difference between the two values (If we select 3months ,it has to take min and max value of the selection) ,if there is no diff between the two values it has to return "No Diff", if the value is less "Decresed", if it is more "Increased".

Briefly - If i select April,may and june , it has to take april and june data and if the april value is more it should return "Decreased", If june is more than April it should return "Incresed".

Regards,

Suresh. 

[Anonymous · Accepted solution]

Step 1: Add a calculated column "MonthNumber"

MonthNumber = SWITCH([Month],
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apri", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sep", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12,
0
)

Define 4 measures (Not all of them are necessary. You can directly write the final measure by combining the formula into one.

Measure 1: Initial Figure

InitialFigure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, Table1[MonthNumber] = MIN ( Table1[MonthNumber] ) )
)

Measure 2: Final Figure

FinalFigure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, Table1[MonthNumber] = MAX ( Table1[MonthNumber] ) )
)

Measure 3: Difference

Difference = [FinalFigure] - [InitialFigure]

Measure 4: Trend

Trend = if([Difference]<0,"Decrease",IF([Difference]=0,"No Change","Increased"))

Now based on your month selection, these measures will give you the results.

Disclaimer: In the SWITCH() function, I have assigned 1 for January, 2 for February and so on... If you are following a financial year calendar like Apr to Mar, then you will have to assign 1 for Apr, 2 May, etc... and 10 for Jan, 11 for Feb, and 12 for Mar. Only then the beginning and ending period will be calculated correctly.

[Cynwaa]

Hello, I know a way to achieve this but it is perhaps not the smartest way.

1. Instead of your NUMBER and MONTH column, I would add a DATE column using real date format (e.g. 2012-06-01)

2. add a measure:

Difference =

VAR Date1 = CALCULATE( MIN('Table'[Date]);ALLSELECTED('Table'))

VAR Date2 = CALCULATE( MAX('Table'[Date]);ALLSELECTED('Table'))

VAR Eearlier = LOOKUPVALUE('Table'[Value];'Table'[Date];Date1)

VAR Later = LOOKUPVALUE('Table'[Value];'Table'[Date];Date2)

RETURN(

IF(Eearlier=Later;"No Diff";

IF(Eearlier>Later;"Decreased";"Increased")))

[Anonymous]

Hi Cynwaa,

Thanks for your reply. Still  getting the below error "Can't load the data for the visual".

Regards,

Suresh.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

Attached sample file for your reference.It seems sreenathv's workaround is work for you.

Regards,

[Anonymous · Accepted solution]

Step 1: Add a calculated column "MonthNumber"

MonthNumber = SWITCH([Month],
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apri", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sep", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12,
0
)

Define 4 measures (Not all of them are necessary. You can directly write the final measure by combining the formula into one.

Measure 1: Initial Figure

InitialFigure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, Table1[MonthNumber] = MIN ( Table1[MonthNumber] ) )
)

Measure 2: Final Figure

FinalFigure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( Table1, Table1[MonthNumber] = MAX ( Table1[MonthNumber] ) )
)

Measure 3: Difference

Difference = [FinalFigure] - [InitialFigure]

Measure 4: Trend

Trend = if([Difference]<0,"Decrease",IF([Difference]=0,"No Change","Increased"))

Now based on your month selection, these measures will give you the results.

Disclaimer: In the SWITCH() function, I have assigned 1 for January, 2 for February and so on... If you are following a financial year calendar like Apr to Mar, then you will have to assign 1 for Apr, 2 May, etc... and 10 for Jan, 11 for Feb, and 12 for Mar. Only then the beginning and ending period will be calculated correctly.

[vanessafvg]

@Anonymous  are you looking at the min and max within one year or more than one year?