5 Consecutive Day-Over-Day Sales Increase

[FredYeung_856]

Dear all,

I have built a PBI report showing the day-over-day increase (the red triangles) of our stores' sales. Is there any way to highlight those stores with day-over-day increases for 5 consecutive days?

Please kindly advise, thanks

Fred

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

And because I am using my sample, I tried to create a measure for finding Four-days-consecutive-increase-store.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create your own measure for your data model.

Sales measure: = 
SUM( Sales[Sales] )

DoD increase: = 
VAR _yesterdaysales =
    CALCULATE ( [Sales measure:], 'Calendar'[Date] = MAX ( 'Calendar'[Date] ) - 1 )
VAR _todaysales = [Sales measure:]
RETURN
    IF ( _todaysales > _yesterdaysales, 0, 1 )

Four days increase consecutive: = 
VAR _newtable =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( ALL ( Sales ), Sales[Store] = MAX ( Store[Store] ) ),
            'Calendar'[Date]
        ),
        "@DoD_condition", [DoD increase:]
    )
VAR _addcumulatevalue =
    ADDCOLUMNS (
        _newtable,
        "@cumulate_condition",
            SUMX (
                FILTER ( _newtable, 'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] ) ),
                [@DoD_condition]
            )
    )
VAR _groupbycumulatecondition =
    FILTER (
        GROUPBY (
            _addcumulatevalue,
            [@cumulate_condition],
            "@fourormoreconsecutive", SUMX ( CURRENTGROUP (), 1 )
        ),
        [@fourormoreconsecutive] > 4
    )
VAR _condition =
    COUNTROWS ( _groupbycumulatecondition ) <> BLANK ()
RETURN
    _condition

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

And because I am using my sample, I tried to create a measure for finding Four-days-consecutive-increase-store.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create your own measure for your data model.

Sales measure: = 
SUM( Sales[Sales] )

DoD increase: = 
VAR _yesterdaysales =
    CALCULATE ( [Sales measure:], 'Calendar'[Date] = MAX ( 'Calendar'[Date] ) - 1 )
VAR _todaysales = [Sales measure:]
RETURN
    IF ( _todaysales > _yesterdaysales, 0, 1 )

Four days increase consecutive: = 
VAR _newtable =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( ALL ( Sales ), Sales[Store] = MAX ( Store[Store] ) ),
            'Calendar'[Date]
        ),
        "@DoD_condition", [DoD increase:]
    )
VAR _addcumulatevalue =
    ADDCOLUMNS (
        _newtable,
        "@cumulate_condition",
            SUMX (
                FILTER ( _newtable, 'Calendar'[Date] <= EARLIER ( 'Calendar'[Date] ) ),
                [@DoD_condition]
            )
    )
VAR _groupbycumulatecondition =
    FILTER (
        GROUPBY (
            _addcumulatevalue,
            [@cumulate_condition],
            "@fourormoreconsecutive", SUMX ( CURRENTGROUP (), 1 )
        ),
        [@fourormoreconsecutive] > 4
    )
VAR _condition =
    COUNTROWS ( _groupbycumulatecondition ) <> BLANK ()
RETURN
    _condition

[FredYeung_856]

Thanks Jihwan

May I consult with you on how to just display the stores if the four consecutive days condition is "TRUE"?

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

I am not sure how you want to display, but please check the below picture and the attached file.

Sales measure only true: = 
IF ( [Four days increase consecutive:] = TRUE (), [Sales measure:], BLANK () )

[amitchandak]

@FredYeung_856 , Assume you already have DOD measure with help from date table try a measure like

Rolling 5 = If( calculate(Sumx(Values('Date'[Date]),if([DOD] >0,1,0)),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-5,day)) = 5, 1, blank())

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[FredYeung_856]

Thanks  @amitchandak 

However, I encounter the following error. Is there anything i did wrong?