Solved: Analyse increasing and decreasing MoM trends - dep... - Page 2

vanlessing · ‎08-28-2018

Hi,

I am looking for a report that shows how many months in a row a value did increase. 1 = MoM did increase.

If the there is no increase, the count of months should start again when there is an increase again.

In the screenhot in June 2016 should be "1" instead of "5".

I got two measures. I guess the DATESYTD needs to be replaced somehow but I couldnt find a working filter(date, ???)

Any suggestions?

Thanks

MoM Increasing? =

IF(DIVIDE([Value],[Value Previousmonth])

>1,1,0)

Month(s) increasing =

CALCULATE(

SUMX(

SUMMARIZE('Date', 'Date'[Month], "ABCD", [MoM Increasing?]), [ABCD]),

DATESYTD('Date'[Date],"31/12"))

TomMartens · ‎08-28-2018

Hey,

I couldn't resist and I created a very simplified table (it would be really beneficial if your calendar table contains a running monthindex, meaning January 2016 (the 1st month in your calendar table) has the value 1 and February 2017 the value 14).

I created this measure

ABC = 
SUMX(
    VALUES(Table1) // of course this should be a table expression representing the months from the calendar table
    ,
    var currentChek = 'Table1'[check]
    var currentMonthIndex = 'Table1'[monthindex]
    return
    IF(currentChek = 0
        ,0
        ,
        var firstMonth = CALCULATE(
            MAX('Table1'[monthindex])
            ,'Table1'[monthindex] < currentMonthIndex
            ,'Table1'[check] =  0
        )+1
        return
        CALCULATE(
            SUM(Table1[check])
            ,FILTER(
                ALL('Table1'[monthindex])
                ,'Table1'[monthindex] >= firstMonth && 'Table1'[monthindex] <= currentMonthIndex
            )
        )
    )
)

Here you will see the result (my assumption, if there is no growth the value will be 0):

I'm also wondering what you want to see as a Result, could be anything from

BLANK
the number of consecutive seqquences 3 in the selected months
the max value (representing the longest chain of consecutive growth)

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

TomMartens · ‎09-03-2018

Hey @vanlessing,

hey not sure if you already solved it, but nevertheless here is my solution utilizing measures.

I created a column "RunningMonthIndex" in your Calendar table:

RunningMonthIndex = 
var CalendarYearStart = YEAR(Min('Calendar'[Date]))
var CurrentYear = YEAR('Calendar'[Date])
return
(CurrentYear - CalendarYearStart) * 12 + MONTH('Calendar'[Date])

This colum "numbers" the months across all years. It's basically the foundation for the sequence as you want the sequence for months.

I also created this measure that marks the months where no growth happens, this marks the last month before a sequence starts.

MoM check Nongrowth = 
IF(OR(NOT(ISBLANK([SalesValue])),NOT(ISBLANK([SalesPM]))),
IF(DIVIDE([SalesValue],[SalesPM])>1,BLANK(),1)
)

It's the "inverse" of your measure "MoM Increasing?" with a subtle difference, instead of returning 0 for the false part it returns BLANK. This allows to determine the LASTNONBLANK month, the last month before the starting of a sequence.

The measure sequence uses all the stuff mentioned above.

Inside the measure the variable finalvalue just counts the month from the current month down to the month after the last non-growth month.

Sequence = 
IF(OR(HASONEFILTER('Calendar'[RunningMonthIndex]),ISCROSSFILTERED('Calendar'[RunningMonthIndex]))
    ,
    var filterIdxMonth = MAX('Calendar'[RunningMonthIndex])
    var allMonthIdx = 
            CALCULATETABLE(
                VALUES('Calendar'[RunningMonthIndex])
                ,FILTER(
                    ALL('Calendar')
                    ,'Calendar'[RunningMonthIndex] < filterIdxMonth
                )
            )
    var startmonth =
        CALCULATE(
            LASTNONBLANK('Calendar'[RunningMonthIndex],[MoM check Nongrowth])
            ,ALL('Calendar')
            ,allMonthIdx
            )
    var finalValue = 
        COUNTROWS(
            FIlTER(
                allMonthIdx
                ,'Calendar'[RunningMonthIndex] >= startmonth && 'Calendar'[RunningMonthIndex] <= filterIdxMonth
            )
        )
    return
    IF([MoM Increasing?] = 0,0,finalValue) 
,BLANK()
)

Done 🙂

Here is a little screenshot

Hopefully this is what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post