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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Jansco
Helper I
Helper I

Count consecutive positives and negatives

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.
3 REPLIES 3
v-shex-msft
Community Support
Community Support

Hi @Jansco ,

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 )
                && WEEKNUM ( [Date], 1 ) = WEEKNUM ( currDate, 1 )
        )
    )
VAR preWeek =
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( prevDate )
                && 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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?

 

Num Consec Ups 60 Days =

 

SWITCH(

    TRUE (),

    [*Sales Last 7 Days vs 60 Day Avg]>0

    && [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 8 Weeks Prior vs 60 Day Avg]>0 ,8,

 

   [*Sales Last 7 Days vs 60 Day Avg]>0

    && [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0,7,

 

    [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 8 Weeks Prior vs 60 Day Avg]>0 ,7,

 

   [*Sales Last 7 Days vs 60 Day Avg]>0

    && [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0,6,

 

    [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 8 Weeks Prior vs 60 Day Avg]>0 ,6,

 

    [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0,6,

 

     [*Sales Last 7 Days vs 60 Day Avg]>0

    && [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0,5,

 

    [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0,5,

 

    [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0,5,

 

    [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 8 Weeks Prior vs 60 Day Avg]>0 ,5,

 

    [*Sales Last 7 Days vs 60 Day Avg]>0

    && [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0, 4,

 

    [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0, 4,

 

    [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0, 4,

 

    [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0, 4,

 

    [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 8 Weeks Prior vs 60 Day Avg]>0 ,4,

 

    [*Sales Last 7 Days vs 60 Day Avg]>0

    && [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0,3,

 

    [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0,3,

 

     [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0,3,

 

    [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0,3,

 

    [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0,3,

 

    [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 8 Weeks Prior vs 60 Day Avg]>0 ,3,

 

    [*Sales Last 7 Days vs 60 Day Avg]>0

    && [*Sales 2 Weeks Prior vs 60 Day Avg]>0,2,

 

    [*Sales 2 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 3 Weeks Prior vs 60 Day Avg]>0,2,

 

    [*Sales 3 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 4 Weeks Prior vs 60 Day Avg]>0,2,

 

    [*Sales 4 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 5 Weeks Prior vs 60 Day Avg]>0,2,

 

    [*Sales 5 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 6 Weeks Prior vs 60 Day Avg]>0,2, 

 

    [*Sales 6 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 7 Weeks Prior vs 60 Day Avg]>0,2,

 

    [*Sales 7 Weeks Prior vs 60 Day Avg]>0

    && [*Sales 8 Weeks Prior vs 60 Day Avg]>0 ,2,

 

BLANK())

Hi @Jansco ,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.