Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
subhashree_r
Helper I
Helper I

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.Capture 7.PNG

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.

5 REPLIES 5
Anonymous
Not applicable

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" )

 

 

subhashree_r
Helper I
Helper I

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.

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.

v-joesh-msft
Solution Sage
Solution Sage

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--Bszl6JNsuaysv... 

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.

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.