The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Hi @Anonymous
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.
Hi @Anonymous
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.
@Anonymous are you looking at the min and max within one year or more than one year?
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
94 | |
86 | |
69 | |
65 |
User | Count |
---|---|
232 | |
128 | |
116 | |
82 | |
82 |