Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Attached sample file for your reference.It seems sreenathv's workaround is work for you.
Regards,
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:
Hi Cynwaa,
Thanks for your reply. Still getting the below error "Can't load the data for the visual".
Regards,
Suresh.
Attached sample file for your reference.It seems sreenathv's workaround is work for you.
Regards,
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.
@SureshPantra are you looking at the min and max within one year or more than one year?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |