Dynamic text card visual based on filter selection

[Jayaselvan]

Hi All,

Please check the link below for my query.

https://docs.google.com/document/d/e/2PACX-1vTKGwtjwHXJaRclnDTU9R1prmqwqw21n5smiBKwqg_ZJDETN5_MNkVO9...

Please download the pbi file from the below link.

https://drive.google.com/file/d/1F2LYm6qX6it6eq1jDkASYBRZtFvZ6tme/view?usp=sharing

[dk_dk]

In Scenario 2 do you always compare the growth to July and August 2022?

[Jayaselvan]

Please help!

@amitchandak, @lbendlin , @Ahmedx , @Ritaf1983 , @Idrissshatila 

[Jayaselvan]

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_dk]

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.

[Jayaselvan]

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.