Count consecutive positives and negatives

Question

I have measures (not columns) that total sales for the last 7 days, the 7 days before that, the 7 days before that (3 weeks prior) and the 7 days before that (4 weeks prior).

I need a count of whether weekly consecutive sales were up or down (total num weeks up and total Num weeks down will be measures in my table visual).

Also, this data is by customer in my table visual.

anonymous · Answer

Hi&nbsp;Jansco&nbsp;,
You can consider to use 'weeknum' and 'year' as group to summary your records, then you can compare the difference between current week and previous week.
Diff =
VAR currDate =
    MAX ( Calendar[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ), DAY ( curDate ) - 7 )
VAR curWeek =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                &amp;&amp; WEEKNUM ( [Date], 1 ) = WEEKNUM ( currDate, 1 )
        )
    )
VAR preWeek =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( prevDate )
                &amp;&amp; WEEKNUM ( [Date], 1 ) = WEEKNUM ( prevDate, 1 )
        )
    )
RETURN
    curWeek - preWeek

If above no help, please provide some sample data and expected result for test.
Regards,
Xiaoxin Sheng

jansco · Answer

My measures are set up like this:SalesLast7 =calculate(sum(‘SalesTable’[Sales]),(Datesinperiod(date[date], selevtedvslue (date[date])-1,-7,day)SalesTwoWeeksPrior ==calculate(sum(‘SalesTable’[Sales]),(Datesinperiod(date[date], selevtedvslue (date[date])-8,-7,day)I discovered a formula like the one below will work, but it takes FOREVER. Is there a faster way?&nbsp;Num Consec Ups 60 Days =&nbsp;SWITCH(&nbsp;&nbsp;&nbsp; TRUE (),&nbsp;&nbsp;&nbsp; [*Sales Last 7 Days vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 8 Weeks Prior vs 60 Day Avg]&gt;0 ,8,&nbsp;&nbsp;&nbsp; [*Sales Last 7 Days vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0,7,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 8 Weeks Prior vs 60 Day Avg]&gt;0 ,7,&nbsp;&nbsp;&nbsp; [*Sales Last 7 Days vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0,6,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 8 Weeks Prior vs 60 Day Avg]&gt;0 ,6,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0,6,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [*Sales Last 7 Days vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0,5,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0,5,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0,5,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 8 Weeks Prior vs 60 Day Avg]&gt;0 ,5,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales Last 7 Days vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0, 4,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0, 4,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0, 4,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0, 4,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 8 Weeks Prior vs 60 Day Avg]&gt;0 ,4,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales Last 7 Days vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0,3,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0,3,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0,3,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0,3,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0,3,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 8 Weeks Prior vs 60 Day Avg]&gt;0 ,3,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales Last 7 Days vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0,2,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 2 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0,2,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 3 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0,2,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 4 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0,2,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 5 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0,2,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 6 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0,2,&nbsp;&nbsp;&nbsp;&nbsp; [*Sales 7 Weeks Prior vs 60 Day Avg]&gt;0&nbsp;&nbsp;&nbsp; &amp;&amp; [*Sales 8 Weeks Prior vs 60 Day Avg]&gt;0 ,2,&nbsp;BLANK())

anonymous · Answer

Hi&nbsp;Jansco&nbsp;,
If you can please share a sample pbix file for test, it is hard to modify dax formula without any detail sample data.
Regards,
Xiaoxin Sheng

Forum Discussion

Count consecutive positives and negatives

3 Replies

Related content

Negative to Positive or Positive to Negative

Consecutive sum for the positive and negative values

IF Negative value

Convert positive values to negative

Positive and negative filter