Identify Consecutive Changed (increased or dropped) Items

[h_l]

Hi,

When use a table or similar visual, is it possible to list the items with metrics those have 3 weeks consecutive changes? either growth or decline. Here is the sample file: sample excel file 

E.g. in the 1st table (by URL):

When user selects Week "17", expect table list the Yellow highlighted one as "3 weeks consecutive decline".

When user selects Week "17", expect table list the Red font items from week 15 to 17 as "3 weeks consecutive growth".

When user selects Week "16", expect table list the the orange highlighted one as "3 weeks consecutive decline".

When user selects Week "16", expect table list the Red font items from week 14 to 16 as "3 weeks consecutive growth".

Same logic to the 2nd table (by Query).

sample excel file (same to the beginning one)

Raw data as screen capture.

Additional Topic:

Is it possible to allow user to select "how many consecutive weeks want to be checked".

When user input 3 / 4 / 5, then, identify the data 3 / 4 / 5  Weeks before the current selected week.

Thanks in advance.

[prienstra]

This is an very nice en powerfull script. I changed it to an Excel version and changed the dax code a little bit. But I have some questions. I am unable to change this myself. 

- When I choose 2 years, but er isn't data available for the 2 years, is it possible to not show that casename.
- When I choose some years and all the years are the same numbers, that is than increased (or the same) and not mixed.
- When I choose 3 years and there is data available for 2 years (and for 1 year not), calculated then based on the 2 available years.

In the file I have some examples which what is my meaning.

Excel file:
https://www.filemail.com/d/gjvwzenthhbariw 

[OwenAuger · Accepted solution]

Hi again @h_l 

Attached is a sample PBIX illustrating what I hope is close to what you're wanting 🙂

When interpreting your requirements, I took it that "3 consecutive weeks of increase" as at week 17 means weeks 15, 16 and 17 form an increasing sequence, i.e. we can ignore the movement from week 14 to 15.

The end result looks like this:

There were a few steps I followed to get this working. I won't list all of the DAX here as it is a bit longwinded and you can see it in the PBIX 🙂

1. Loaded your data from Excel into a table called Metrics.
2. Created a 'Date' table (in DAX for now) containing various columns including Year, WeekNum and Week Index. Week Index is an index that increments for each week beginning Sunday, so doesn't repeat across years.
3. Created a 'Reference Date' table that is a copy of 'Date' with column names prefixed with "Reference". This table is used to select the week that will be the reference week at which you will measure growth/decline. I created an inactive relationship between these two tables but that may not be strictly needed.
4. Created a 'Consecutive Weeks' table which is used as a parameter table to select the number of consecutive weeks.
5. Created a measure Impressions which is the sum of the Impression columnn.
6. Created a measure Impression WoW Variance (used in one version of the subsequent measures) that is the week-on-week change in Impressions.
7. Create measures called Direction Type and Direction Type 2. These both do the same thing. Direction Type performs better but Direction Type 2 is easier to read.
   • If Impressions over the consective weeks form a strictly increasing sequence, then return 1.
   • If Impressions over the consective weeks form a strictly decreasing sequence, then return -1.
   • Otherwise return 0
8. Created an Impression Segments table. This table defines the segments that you care about, and looks like this:
   
9. Create the segmentation measures, based on the pattern from DAX Patterns. I have listed one of the measures below.
10. I also created a measure Display Date Range to aid filtering the date range on visuals to just the "consecutive weeks range".  This measure has the value 1 when the current date range intersects the "consecutive weeks range". I actually applied it to the visuals as a TopN filter on Week Index. Measure is shown below.
11. Now the Impression Segmented by... measures can be used in a visual that groups by Segment and URL or Query, producing the result shown above.

Display Date Range = 
VAR ConsWeeks =
    SELECTEDVALUE ( 'Consecutive Weeks'[Consecutive Weeks] )
VAR DateRangeMax = 
    MAX ( 'Reference Date'[Reference Date] )
VAR DateRange =
    DATESINPERIOD ( 'Date'[Date], DateRangeMax, -ConsWeeks * 7, DAY )
VAR InDateRange = 
    CALCULATE (
        NOT ISEMPTY ( 'Date' ),
        KEEPFILTERS ( DateRange )
    )
RETURN
    InDateRange

Impressions Segmented by URL = 
VAR ItemsInSegment =
    FILTER (
        ALLSELECTED ( Metrics[URL] ),
        VAR DirectionOfCurrentItem = [Direction Type]
        VAR SegmentForCurrentItem =
            FILTER (
                'Impressions Segments',
                'Impressions Segments'[Direction] = DirectionOfCurrentItem
            )
        VAR InSegment = NOT ISEMPTY ( SegmentForCurrentItem )
        RETURN InSegment
    )
VAR Result =
    CALCULATE (
        [Impressions],
        KEEPFILTERS ( ItemsInSegment )  -- Applies filter for segmented customers 
    )
RETURN Result

Hopefully that's useful, and you can tweak to suit your exact needs.

All the best!

Owen

[h_l]

Hi @OwenAuger ,

May I come back and ask:

When I load 2022 data into the DB, seems the function does not work well. (When I test in 2021 use 2021 data, everything works well.)

Take 2 examples:

• 3 is selected as consecutive weeks, but in the data table followed by, there are more than 3 weeks.

• In the "Consecutive Increase", the listed items seemed not correct, you can see some are not "consecutive increase".

I tried to investigate the reason but so far still not be able to find the way to "debug".

Would you mind to provide an idea?

Have a safe and joyful day.

[OwenAuger]

Hi @h_l 

Good to hear from you again 🙂

The calculation logic should work, but there could be something going on with how the tables have been rolled forward to the next year.

You could double-check that 'Date' and 'Reference Date' have been correctly extended into 2022, and the Week Index column should contain a unique index for each week.

Also, for the Columns of the matrix visuals, perhaps use a Year-Week column rather than just Week Index. Or include both Year and Week in Columns.

If you can post a sanitised PBIX (or DM me), I may be able to offer some better suggestions.

Regards,

Owen

[h_l]

Hi @OwenAuger , 

Appreciate your reply and sorry for this so late post...I was struggling in traveling and related matters. 

I will look into follow by your advise, and may come back or send message to you if there is further questions.

I hope you have safe and joyful days.

Thanks again！

[h_l]

Hi Owen,

just come back to let you know that the solution is working well.

Thanks again!

H

[h_l]

Hi @OwenAuger , first of all, please accept my sincere appreciation to your time and great effort to help, so I come to reply you at the first second I see your reply.

Per your interpreting to:

I took it that "3 consecutive weeks of increase" as at week 17 means weeks 15, 16 and 17 form an increasing sequence, i.e. we can ignore the movement from week 14 to 15.

 - It is correct, as week 17 is selected, then the increase from week 14 to 15 can be ignored. But when Week 16 is selected, then Week 14-15-16 shall be identified and listed.

As the solution is a little complicate to me, I will take sometime to investigate and understand it. Will come back and let you know the result.

Thanks again for your time, have a good day!

Best,

H

[OwenAuger]

Hi @h_l 

Something like a Dynamic Segmentation pattern will work here (see this page on DAX Patterns for the general idea).

Normally that involves creating a disconnected segmentation table. In your case, the segments could be  something like "Consecutive growth", "Consecutive decline" and "Other", with the number of weeks input via a separate parameter slicer.

I can provide some suggested measures, but probably best to do using your actual data.

The link to your Excel file leads to a "file deleted" page. Could you re-upload?

Regards,

Owen

[h_l]

Hi Owen,

Thanks for your reply and clarification, I also checked some posts in this great community with super help users like you, but seems they didn't really meet this scenario.

Here is the updated sample data link.

Sample Excel - Updated 

Will check the page you referred later.

Have a good day!