Forum Discussion
Prudhviraj
8 years agoFrequent Visitor
Dynamic calculation based on Slicer value.
Hi All, We have to create report where user can select Month_Name, based on the selection we have to show selected month and previous month of selected month in the report. And all of that I need...
- 8 years ago
Currently, we cannot achieve this requirement in table. To work around this, we could achieve the similar requirement in table visual.
create a table and some columns.
Table = FILTER ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ), DAY ( [Date] ) = 1 ) MonthName = FORMAT ( 'Table'[Date], "MMMM" ) MonthNumber = MONTH ( 'Table'[Date] )Create two measure in your original table.
Filter = VAR selectmonth = IF ( HASONEFILTER ( 'Table'[MonthName] ), MAX ( 'Table'[MonthNumber] ), BLANK () ) VAR Previous_month = selectmonth - 1 VAR check = IF ( MAX ( Table1[MonthNumber] ) = selectmonth || MAX ( Table1[MonthNumber] ) = Previous_month, 1, 0 ) RETURN checkChange = VAR selectmonth = IF ( HASONEFILTER ( 'Table'[MonthName] ), MAX ( 'Table'[MonthNumber] ), BLANK () ) VAR Previous_month = selectmonth - 1 VAR currenttype = MAX ( Table1[Type] ) VAR currentregion = MAX ( Table1[Region] ) VAR currentmonth = MAX ( Table1[MonthNumber] ) RETURN IF ( MAX ( 'Table1'[MonthNumber] ) = Previous_month, BLANK (), IF ( MAX ( Table1[Amount] ) - LOOKUPVALUE ( Table1[Amount], Table1[Type], currenttype, Table1[Region], currentregion, Table1[MonthNumber], Previous_month ) > 0, "increase", "decrease" ) )User Filter measure in you visual filter.
Regards,
Charlie Liao
Prudhviraj
8 years agoFrequent Visitor
Thank you Charlie.
Ashish_Mathur
8 years agoSuper User
Hi,
This can be done in MS Excel using PowerPivot and CUBE functions.