Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"))
Solved! Go to Solution.
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
Regards,
Tom
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
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
Regards,
Tom
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
Hey @vanlessing,
can you please also share your xlsx file that contains your sample data for the table "base".
Regards,
Tom
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
Hopefully this is what you are looking for.
Regards,
Tom
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
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
Hey,
@vanlessing thanks for sharing the screenshot! This is a interesting usecase for consecutive series.
Regards,
Tom
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |