Forum Discussion

subhashree_r's avatar
subhashree_r
Icon for Helper I rankHelper I
6 years ago

Like for Like Analysis Slicer

Hey People,

    I would like to achieve a slicer for Like for Like Analysis. Given a image below for reference.

If the enable is selected then

1. stores that were newly opened in the current year should not show.

2. stores that were closed in the previous year should also be eliminated.

For Eg:

Consider I have 10 stores in 2016 (Previous Year)

        1. I closed one of these stores 

 In 2017(Current Year)  I opened three new stores 

Now when I compare CY Vs PY along with the enable slicer on then I should have comparison between the 9 stores that were present in the previous and also present in the current year only.

Need Help

5 Replies

  • v-joesh-msft's avatar
    v-joesh-msft
    Icon for Solution Sage rankSolution Sage
    6 years ago

    Hi subhashree_r ,

    You can create a calculated column like the following:

    EnableDisable =
VAR TA =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Year] ),
        ALLEXCEPT ( 'Table', 'Table'[Stores] )
    )
RETURN
    IF ( TA = 1, "Enable", "Disable" )

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Eb4--Bszl6JNsuaysv6D3_sBk0D1zLhg1JxtKIyOX1Gk6g?e=cWNCkV 

    Best Regards,

    Community Support Team _ Joey
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • subhashree_r's avatar
      subhashree_r
      Icon for Helper I rankHelper I
      6 years ago

      Hey v-joesh-msft ,

         I'am finding it difficult to understand the concept associated with the dax, can you please guide me in understanding the same.

  • subhashree_r's avatar
    subhashree_r
    Icon for Helper I rankHelper I
    6 years ago

    Another thing about this visual is I would not want to use multiple measure for induvidual visuals rather a single slicer selection that would filter down the entire data to just the stores that comply to Like For Like Aanlysis.

    • v-joesh-msft's avatar
      v-joesh-msft
      Icon for Solution Sage rankSolution Sage
      6 years ago

      Hi subhashree_r ,

      My demo is based on what you describe and what your model might look like. Could you share your sample data so that it can help solve the problem better?

      Best Regards,

      Community Support Team _ Joey
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi v-joesh-msft.

     

    Your reply has been very helpful for me, thanks! However, it only seems to work if there is a comparison between current year and previous year. I'm working with multiple years (2018, 2019,2020 and soon 2021) where I would like to filter on a ISO-weeklevel OR month instead of full year. E.g. compare this week with same week last year Like for Like

    I've been trying to alter your formula a bit, but didn't get what I was looking for yet. Maybe because I have to compare 'year & week' of current year with 'year & week' of previous year instead of only week? See below. Can you please help me out?

     

    EnableDisable =
VAR TA =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ISOweek] ),
        ALLEXCEPT ( 'Table', 'Table'[Stores] )
    )
RETURN
    IF ( TA = 1, "Incomplete comparison", "Like-for-like" )