Forum Discussion
Dynamic text card visual based on filter selection
In Scenario 2 do you always compare the growth to July and August 2022?
- Jayaselvan2 years agoHelper II
No it will be dynamic, If you see the month filter, I have selected Sep-2022. In that case it should compare with Aug-2022(previous month) and July-2022(before 2 months).
- dk_dk2 years agoSuper User
Hi Jayaselvan
I was able to make the logic for scenario 2, where there is a single country selected. I don't have any more time today to work on scenario 1, but maybe someone else can help you with it or otherwise I can look at it later.
You can create a measure like this:CardVisual Text = //single or multi select VAR singlecountry = HASONEVALUE(Customer[country]) // single country scenario VAR SelectedCountry = SELECTEDVALUE(Customer[country]) // selected month VAR slicerSelection = MAX(Orders[Tr_Mn_Yr]) //Previous month formatted as text VAR PrevMonth = SWITCH(MONTH(DATEVALUE(slicerSelection)-28),1,"Jan",2,"Feb",3,"Mar",4,"Apr",5,"May",6,"Jun",7,"Jul",8,"Aug",9,"Sep",10,"Oct",11,"Nov",12,"Dec") VAR PrevYear = YEAR(DATEVALUE(slicerSelection)-28) VAR slicerSelectionpr1= PrevMonth&" "&PrevYear //2 months ago formatted as text VAR PrevMonth2 = SWITCH(MONTH(DATEVALUE(slicerSelection)-56),1,"Jan",2,"Feb",3,"Mar",4,"Apr",5,"May",6,"Jun",7,"Jul",8,"Aug",9,"Sep",10,"Oct",11,"Nov",12,"Dec") VAR PrevYear2 = YEAR(DATEVALUE(slicerSelection)-56) VAR slicerSelectionpr2= PrevMonth2&" "&PrevYear2 //revenue in selected country last month VAR selCountryPrev = CALCULATE(SUM(Orders_Product_Level[Revenue]),Customer[country]=SelectedCountry,Orders[Tr_Mn_Yr] = slicerSelectionpr1) //revenue in selected country 2 months ago VAR selCountryPrev2 = CALCULATE(SUM(Orders_Product_Level[Revenue]),Customer[country]=SelectedCountry,Orders[Tr_Mn_Yr] = slicerSelectionpr2) //revenue in selected country in selected month VAR selCountryCurrent = CALCULATE(SUM(Orders_Product_Level[Revenue]),Customer[country]=SelectedCountry,Orders[Tr_Mn_Yr] = slicerSelection) //growth claculation logic VAR growthSincePrev2 = ROUND(((selCountryCurrent-selCountryPrev2)/selCountryPrev2)*100,2) VAR prev2change = IF(growthSincePrev2>0,"increased","decreased") VAR growthSincePrev = ROUND(((selCountryCurrent-selCountryPrev)/selCountryPrev)*100,2) VAR prevchange = IF(growthSincePrev>0,"increased","decreased") //text concatenation VAR singleCountryText = "The " & SelectedCountry & " has " & prevchange & " by " & growthSincePrev & "% when compared to " & slicerSelectionpr1 & ", and " & prev2change & " by " & growthSincePrev2 & "% when compared to " & slicerSelectionpr2 &"." // multiple countries TO BE FINISHED VAR RevenueCurrent = CALCULATE(SUM(Orders_Product_Level[Revenue]),Orders[Tr_Mn_Yr]=slicerSelection) RETURN // if single selection show the text output for that, else show placeholder IF(singlecountry,singleCountryText,"Multiple selections logic to be done")and add the measure to a card visual.
If multiple values are selected it will show a placeholder text, if a single country then it will show the described scenario. See my examples:
UK, Sept
USA, Nov:
I hope this helps.- Jayaselvan2 years agoHelper II
Thank you. But the green and red color change is not applied. Could you please help me on that as well. If you have time could you please help on scenario 1 as well.
- Jayaselvan2 years agoHelper II
Please help!