Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Difference between the min and Max Value.

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...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Cynwaa,

     

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

     

    Regards,

    Suresh.