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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vanlessing
Frequent Visitor

Analyse increasing and decreasing MoM trends - depending on variable previous time periods

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"))

 

 

PBI Screen.png       

 

 

 

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

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):

image.png

 

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

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

image.png

 

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

12 REPLIES 12
TomMartens
Super User
Super User

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):

image.png

 

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

Hi,

that looks perfect. At least I know I couldnt compose that measure in a decade.

 

The use case is lifecycle analysis: we select a month and we want to see the rising (and falling) products, and filter by the count of straight rising months.

 

Thanks

 

Hey,

 

so this is the algorithm, I just realized that another approach has to be used to "summarize" the measure value "MoM Growth?".

 

I will have a closer look to this the next days - work, the woman I love get me distracted 🙂

 

Regards,

To



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

Hey @vanlessing,

 

can you please also share your xlsx file that contains your sample data for the table "base".

 

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

Hi Tom,

 

here is the xlsx file.

https://1drv.ms/x/s!Ah2Q6Sc7-qoLhHT49pizwXGWYHwi

 

I realized later that in your first solution the sum was over a table column.

You are right, I need the sum of the measure "Month(s) increasing"

 

Thanks

vanlessing

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

image.png

 

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

Thanx a lot, Tom

 

it`s working as desired and I integrated the DAX in the our business model. Great insights!

Will post a screenshot tomorrow.

 

I try to get the measure for "decreasing" trends by my own.

 

Proud to be a Datascout.


Regards,

vanlessing

 

Cool!

 

Looking forward for a scrrenshot.

 

Cheers,

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

Hi all,

this is our current use case. We want to detect significant increases (eg sales) over multiple periods.

The selected month in the screenshot is July 2018. The chart however shows all previous months including price and max price for all periods.

Another indicator is the increase from the selected month to the month where the series starts (column 5 in the table).

And from there we start digging deeper.

 

Regards and Tx to Tom

 

pbi_months_increasing.jpg

Hey,

 

@vanlessing thanks for sharing the screenshot! This is a interesting usecase for consecutive series.

 

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
TomMartens
Super User
Super User

Hey,

 

can you pleaes prepare a pbix file that contains sample data, upload the pbix file to onedrive or dropbox and share the link.

I'm also wondering what what you expect for May 2016.

 

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

Hi,

here is a sample file.

Data is different cause I cant send the original values

Hope that helps

 

https://1drv.ms/u/s!Ah2Q6Sc7-qoLhHOYOO6T6knRKb3C

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.