Forum Discussion
change measures using hierarchy slicers.
Hi All,
I have a power bi requirement. I have a date hierarchy slicer with Fiscal Year, Fiscal Quarter, Fiscal Month and Fiscal week as Below.
Now I have a measure to show value of conversations happened in a card.
I have also created separate Previous Quarter, Month, Week measures.
I have to show another value which will be change from previous FY, Previous Quarter, Previous Month and Previous week , but as per slicer selection.
If the slicer is selected as FY 2025, then the value should be( [# Conversation Opened] - [Previous FY]) / [Previous FY], ex. (total for fy 25 - total for fy 24 ) / total for fy 24
If the slicer is selected as Feb, then the value should be( [# Conversation Opened] - [Previous Month ]) / [Previous Month], ex. (total for feb- total jan)/total jan
If the slicer is selected as FW 6, then the value should be( [# Conversation Opened] - [Previous FW]) / [Previous FW], ex. (total for fw6 - total for fw5)/total for fw5.
nemo189 , Try using
Create the base measure for conversations opened:
DAX
# Conversation Opened =
CALCULATE (
DISTINCTCOUNT ( Conversation[CONVERSATIONID] ),
Conversation[CONVERSATIONINITIATOR] = "customer"
)Create measures for previous periods:
Previous FY =
CALCULATE (
[# Conversation Opened],
DATEADD ( DateTable[Date], -1, YEAR )
)Previous Quarter =
CALCULATE (
[# Conversation Opened],
DATEADD ( DateTable[Date], -1, QUARTER )
)Previous Month =
CALCULATE (
[# Conversation Opened],
DATEADD ( DateTable[Date], -1, MONTH )
)Previous Week =
CALCULATE (
[# Conversation Opened],
DATEADD ( DateTable[Date], -1, WEEK )
)Create a measure to detect the selected hierarchy level:
Selected Hierarchy Level =
IF (
ISFILTERED ( DateTable[Fiscal Year] ), "FY",
IF (
ISFILTERED ( DateTable[Fiscal Quarter] ), "Quarter",
IF (
ISFILTERED ( DateTable[Fiscal Month] ), "Month",
IF (
ISFILTERED ( DateTable[Fiscal Week] ), "Week",
"None"
)
)
)
)Create the dynamic change measure:
DAX
Change Measure =
SWITCH (
[Selected Hierarchy Level],
"FY", DIVIDE ( [# Conversation Opened] - [Previous FY], [Previous FY] ),
"Quarter", DIVIDE ( [# Conversation Opened] - [Previous Quarter], [Previous Quarter] ),
"Month", DIVIDE ( [# Conversation Opened] - [Previous Month], [Previous Month] ),
"Week", DIVIDE ( [# Conversation Opened] - [Previous Week], [Previous Week] ),
BLANK()
)
1 Reply
- bhanu_gautam
Super User
nemo189 , Try using
Create the base measure for conversations opened:
DAX
# Conversation Opened =
CALCULATE (
DISTINCTCOUNT ( Conversation[CONVERSATIONID] ),
Conversation[CONVERSATIONINITIATOR] = "customer"
)Create measures for previous periods:
Previous FY =
CALCULATE (
[# Conversation Opened],
DATEADD ( DateTable[Date], -1, YEAR )
)Previous Quarter =
CALCULATE (
[# Conversation Opened],
DATEADD ( DateTable[Date], -1, QUARTER )
)Previous Month =
CALCULATE (
[# Conversation Opened],
DATEADD ( DateTable[Date], -1, MONTH )
)Previous Week =
CALCULATE (
[# Conversation Opened],
DATEADD ( DateTable[Date], -1, WEEK )
)Create a measure to detect the selected hierarchy level:
Selected Hierarchy Level =
IF (
ISFILTERED ( DateTable[Fiscal Year] ), "FY",
IF (
ISFILTERED ( DateTable[Fiscal Quarter] ), "Quarter",
IF (
ISFILTERED ( DateTable[Fiscal Month] ), "Month",
IF (
ISFILTERED ( DateTable[Fiscal Week] ), "Week",
"None"
)
)
)
)Create the dynamic change measure:
DAX
Change Measure =
SWITCH (
[Selected Hierarchy Level],
"FY", DIVIDE ( [# Conversation Opened] - [Previous FY], [Previous FY] ),
"Quarter", DIVIDE ( [# Conversation Opened] - [Previous Quarter], [Previous Quarter] ),
"Month", DIVIDE ( [# Conversation Opened] - [Previous Month], [Previous Month] ),
"Week", DIVIDE ( [# Conversation Opened] - [Previous Week], [Previous Week] ),
BLANK()
)