Forum Discussion

5 Replies

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

    • Jayaselvan's avatar
      Jayaselvan
      Helper 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_dk's avatar
        dk_dk
        Super 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.