Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculated column based on sales

Hi

I need calculated column based on Sales.

1.Need to calculate Current year sales(CY), Current year sales-1(CY-1), Current year sales-2(CY-2) based on Selected year.
2.Expected output column :
          [CY] > 0 && [CY-1] >0 , "Renewal",

     [CY] > 0 && [CY-1] =0 && [CY-2] > 0, "Winback",

     [CY] > 0 && [CY-1] =0 && [CY-2] = 0 ,"Acquisition",

     [CY] = 0 && [CY-1] >0 && [CY-2] > 0, "Erosion"

Please find the Sample PBI file here : https://drive.google.com/drive/folders/1YPRvEhYNFmfWsDWY6nFSASFc4Wp_8-co?usp=sharing
Thanks.

Interesting Links
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    It's suggested to use measure ,like this:

    Flag =
VAR cy1 =
    CALCULATE (
        SUM ( 'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB'[GBP Value] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
VAR cy2 =
    CALCULATE (
        SUM ( 'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB'[GBP Value] ),
        PARALLELPERIOD ( 'Calendar'[Date], -2, YEAR )
    )
VAR curr =
    CALCULATE (
        SUM ( 'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB'[GBP Value] ),
        FILTER (
            'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB',
            'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB'[Year] = YEAR ( TODAY () )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        curr > 0
            && cy1 > 0, "Renewal",
        curr > 0
            && cy1 = 0
            && cy2 > 0, "Winback",
        curr > 0
            && cy1 = 0
            && cy2 = 0, "Acquisition",
        curr = 0
            && cy1 > 1
            && cy2 > 0, "Erosion"
    )

    The final output is shown below:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, Jihwan_Kim 

      Thanks for you reply.
      I need Expected Account Type as a Column because i need to filter the data by using that column in slicer.
      or can we use already created measure in slicer to filter the data.

      Thanks





  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    It's suggested to use measure ,like this:

    Flag =
VAR cy1 =
    CALCULATE (
        SUM ( 'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB'[GBP Value] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
VAR cy2 =
    CALCULATE (
        SUM ( 'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB'[GBP Value] ),
        PARALLELPERIOD ( 'Calendar'[Date], -2, YEAR )
    )
VAR curr =
    CALCULATE (
        SUM ( 'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB'[GBP Value] ),
        FILTER (
            'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB',
            'Compass V_F_VN_Sales_Distr_ZuelligPharma_CompassDB'[Year] = YEAR ( TODAY () )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        curr > 0
            && cy1 > 0, "Renewal",
        curr > 0
            && cy1 = 0
            && cy2 > 0, "Winback",
        curr > 0
            && cy1 = 0
            && cy2 = 0, "Acquisition",
        curr = 0
            && cy1 > 1
            && cy2 > 0, "Erosion"
    )

    The final output is shown below:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.