Forum Discussion
Calculating Growth Based on Multiple or Single Year Chicklet Selection
Hello,
I've been trying to wrap my head around how to go about building a measure based on selectedvalue to calculate year over year growth. I have a chicklet slicer with selections for fiscal years 20, 21, & 22. I want to calculate revenue growth ($) on a Card that will change based on the chicklet selections, which is a multi-select.
If FY20 and FY22 are selected, the card will show (FY22 - FY20) revenue.
If FY20, FY21, AND FY22 are selected, the card will show (FY22-20) revenue.
If no options are selected, the card should show (FY22-20) revenue.
If FY21 is selected, card will show FY21 revenue.
etc...
Appreciate any help!
G23
I think you can cover all the scenarios by just looking at the max and min FY selected.
RevGrowth = VAR MinFY = MIN ( Dim_Date[FiscalYear] ) VAR MaxFY = MAX ( Dim_Date[FiscalYear] ) VAR RevMinFY = CALCULATE ( [Revenue], TREATAS ( { MinFY }, Dim_Date[FiscalYear] ) ) VAR RevMaxFY = CALCULATE ( [Revenue], TREATAS ( { MaxFY }, Dim_Date[FiscalYear] ) ) RETURN IF ( MinFY = MaxFY, RevMaxFY, RevMaxFY - RevMinFY )
2 Replies
- AlexisOlson
Super User
I think you can cover all the scenarios by just looking at the max and min FY selected.
RevGrowth = VAR MinFY = MIN ( Dim_Date[FiscalYear] ) VAR MaxFY = MAX ( Dim_Date[FiscalYear] ) VAR RevMinFY = CALCULATE ( [Revenue], TREATAS ( { MinFY }, Dim_Date[FiscalYear] ) ) VAR RevMaxFY = CALCULATE ( [Revenue], TREATAS ( { MaxFY }, Dim_Date[FiscalYear] ) ) RETURN IF ( MinFY = MaxFY, RevMaxFY, RevMaxFY - RevMinFY ) - vanessafvg
Community Champion
you could probably use concatenex combining your fiscal year values
doing somethink like this
create measure
year value =
IF (
ISFILTERED ( data[year] ),
CALCULATE ( CONCATENATEX ( VALUES ( data[year] ), data[year], "," ) ),
BLANK ()
)and then write a switch statementwhichyear =
SWITCH (
TRUE (),
[year value] = "2020", [FY20],
[year value] = "2021", [FY21],
[year value] = "2020,2021", [FY22-20],
[FY22-20]
)
