Forum Discussion
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 |
| 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.
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.
Hi Anonymous
Attached sample file for your reference.It seems sreenathv's workaround is work for you.
Regards,
5 Replies
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 are you looking at the min and max within one year or more than one year?
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")))Hi Anonymous
Attached sample file for your reference.It seems sreenathv's workaround is work for you.
Regards,
