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
v-caliao-msft
8 years agoMicrosoft Employee
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
check
Change =
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_Mathur8 years agoSuper User
Hi,
This can be done in MS Excel using PowerPivot and CUBE functions.