Consecutive period of sales increase and decrease

[fumi]

Hello

I need help with a problem I am having issues solving

I have a sales table that has daily sales amount. Each row has a store location, staff member, segment and product category broken down by date.

I  also have a measure where  I calculate month over month change in sales and I make use of a separate date table (the date field) for those calculations.

I would like to create a new measure that can show by month for any grouping  I want (e.g. location, staff member, segment or product category), the consecutive count of sales month over month decrease. This measure should be adaptable to the date filters on the page.

So for example, for the month of August 2024 (filter on the page), I would like to see either by staff member, the consecutive  count of month over month sales decrease. So for example, if staff member A had the  following sales from May to August 2024: 120, 100, 80, 60, if I select August 2024 as the date filter, the measure should show 3 for Staff Member A, which  indicates 3 consecutive months of sales decrease month over month.

I would like the same measure to be  used to identify the consecutive  sales decreases  for locations, segments and  product categories as well.

[Anonymous]

Hi @fumi ,

May I ask if you have gotten this issue resolved? If it is solved, please mark the helpful reply or share your solution and accept it as solution.
If not resolved please provide example data. We can better understand the problem and help you.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@fumi tweak the solution as you see fit. Cheers! If this all works out, it is a good candidate for YT videos for my channel - love this kind of challenge.

[parry2k]

@fumi I think I understood your problem, very interesting, here is my stab at it.

First add the following column in your date/calendar table, if a similar column already exists, please ignore it.

Month End Date = EOMONTH ( 'Calendar'[Date], 0 )

Add the following measure to get the consecutive count of months that have more sales than the selected month:

Consective Decrease Count = 
VAR __SelectedMonthSales = [Sales]
VAR __SelectedDate = SELECTEDVALUE ( 'Calendar'[Month End Date] )
VAR __Table =
ADDCOLUMNS (
    ADDCOLUMNS ( 
        SUMMARIZE (
            FILTER ( 
                ALL ( 'Calendar' ), 
                'Calendar'[Date] <= __SelectedDate 
            ),
            'Calendar'[Month End Date],                  
            "@Sales", [Sales] 
        ), 
        "@OFfset", DATEDIFF ( [Month End Date], __SelectedDate, MONTH ),
        "@SelectedMonthSales", __SelectedMonthSales
    ), 
    "@Change", [@SelectedMonthSales] - [@Sales]
)
VAR __FilterMonthWithMoreSales = FILTER ( __Table, [@Change]  < 0 && NOT [@Sales] == BLANK () )
VAR __TableWithRowNumberByMonth = ADDCOLUMNS ( __FilterMonthWithMoreSales, "@RowNumber", ROWNUMBER ( __FilterMonthWithMoreSales, ORDERBY ( [@OFfset] ) ) )
VAR __FilterConsectiveMonths = FILTER ( __TableWithRowNumberByMonth, [@RowNumber] = [@OFfset] ) 
RETURN
COUNTROWS ( __FilterConsectiveMonths )

[fumi]

Thanks @parry2k  for your attempt at this problem
Unfortunately it did not work because I think your formula is calculating the number of times the selected month is lower than the historical months
I want the number of times, there is a decrease in sales month over month up to the selected month. I do have a measure that calculates month over month sales if that helps. And maybe we can have a condition to calculate the number of times that value is less than 0. 

Also, I want it to count as a streak. So if one month the value becomes positive then the count resets.

Hope that helps!

[parry2k]

@fumi @That should be easy. The core logic is the same as hat I provided you just need to replace the change calculation in the dax formula and they should do it.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

One of ways to know the trend up/down and how much up/down is, using LINESTX DAX function.

LINESTX function (DAX) - DAX | Microsoft Learn

[fumi]

Thanks @Jihwan_Kim 

The sales table has product, staff member, segment and location information. They are not different tables

[Jihwan_Kim]

HI,

Please provide your sample pbix file's link, and then I can try to use your sample, instead of using my sample, that is not similar or the same as your scenario.

Thank you.