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.
Dear all,
I have a set of data that has to be evaluated taking in account the previous month.
I am presenting a mock dataset (normal month/value columns) and the desired output.
I need a measure that for each month it evaluates itself PLUS a function of the previous month.
I can't manage this situation as the filters block me to evaluate the previous months.
I appreciate your help,
V
Solved! Go to Solution.
Hi @Anonymous ,
As the screenshot you shared, we should convert the month column from text to number by creating a calculated column.
MonthNumber = SWITCH ( 'Dataset'[Month], "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12 )
Then we can create the following measure to meet what you need.
CurrentMonth = SUM ( 'Dataset'[Value] )
NextMonth/2 = FORMAT ( CALCULATE ( SUM ( 'Dataset'[Value] ), FILTER ( ALL ( 'Dataset' ), 'Dataset'[MonthNumber] = SELECTEDVALUE ( 'Dataset'[MonthNumber] ) + 1 ) ) / 2, "General Number" )
Total = IFERROR ( [CurrentMonth] - [NextMonth/2], BLANK () )
BTW, pbix as attached.
Hi @Anonymous ,
As the screenshot you shared, we should convert the month column from text to number by creating a calculated column.
MonthNumber = SWITCH ( 'Dataset'[Month], "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12 )
Then we can create the following measure to meet what you need.
CurrentMonth = SUM ( 'Dataset'[Value] )
NextMonth/2 = FORMAT ( CALCULATE ( SUM ( 'Dataset'[Value] ), FILTER ( ALL ( 'Dataset' ), 'Dataset'[MonthNumber] = SELECTEDVALUE ( 'Dataset'[MonthNumber] ) + 1 ) ) / 2, "General Number" )
Total = IFERROR ( [CurrentMonth] - [NextMonth/2], BLANK () )
BTW, pbix as attached.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |