cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sebastian
Advocate II
Advocate II

baddest Month of each Year based on value

Hi all,

 

I have a table wich consists of following columns: date, value, area, customer

 

I try to find a way to show the baddest month of each year based on the sum of value.

 

Did someone has an solution how to realize this?

 

 

Thanks.

2 ACCEPTED SOLUTIONS
KHorseman
Community Champion
Community Champion

@Sean I've cracked it! This might be my favorite formula I've ever written. And I couldn't have figured it out without DAX Studio because nesting two contradictory TOPNs is impossible to keep straight in your mind.

 

OK, so here's the setup: same basic data as this whole thread has been about, plus one extra table called RankTable. That table just has a column named [Rank] which is a continuous series of integers, 1-whatever. 1-10 in my example but you could go as high as you need. That table has no relationships to anything. The desired output is that you drop that column as rows in a table visual or something like that, and you place a measure next to it called [Top Months]. On each row, you get the row-th ranked month by sales. So instead of just giving you the name of the month with the worst sales like we started out with, or the best sales, row 8 gives you the 8th best. You can filter RankTable[Rank] so that it only displays the top 5, or only the 7th, or whatever, and it will always return based on whatever number or numbers are showing in that column.

 

This requires one supplemental measure.

RowTest = FIRSTNONBLANK(RankTable[Rank], 1)

Here's the formula*:

 

Top Months = FIRSTNONBLANK(
	SUMMARIZE(
		TOPN(
			1,
			TOPN(
				[RowTest],
				SUMMARIZE(
					SalesTable,
					DateTable[Month of Year],
					"Monthly Sales",
					SUM(SalesTable[value])
				),
				[Monthly Sales],
				0
			),
			[Monthly Sales],
			1
		),
		[Month of Year]
	),
	1
)

Basically it first does a TOPN of the summarized table based on the number fed to it by the RankTable[Rank] row context. So on row 5 it gives you the top 5 highest sales. Then it does a second TOPN going in the reverse direction, giving you the lowest ranked value of that first TOPN set. For row 1 it only has the 1 highest anyway, but for any row below 1 you want to discard everthing higher than that row. Make sense? Probably not, it's hard to picture. Here, have a test file.

 

 If you want the bottom n results (same thing but counting up from the worst) you would just reverse the 1 and 0 from the two nested TOPNs. So...

 

Bottom Months = FIRSTNONBLANK(
	SUMMARIZE(
		TOPN(
			1,
			TOPN(
				[RowTest],
				SUMMARIZE(
					SalesTable,
					DateTable[Month of Year],
					"Monthly Sales",
					SUM(SalesTable[value])
				),
				[Monthly Sales],
				1
			),
			[Monthly Sales],
			0
		),
		[Month of Year]
	),
	1
)

The example file also has two bonus measures for returning the corresponding sales amounts that produced these top and bottom rankings. Enjoy!

 

 

*Well, almost the formula. I did an extra thing for tie breaking in the real formula but I just wanted to show the basics to make the thing work first. In the example file you'll see that the second outer TOPN is not based on [Monthly Sales]; it's actually on [Monthly Sales] + (1 / [Month Index]). Month Index is an extra column in my date table that is just a unique integer index number. I think this means that in a tie the earlier of the tied months will rank lower, but this formula reverses sort orders so many times that I'm still having trouble keeping it straight.

 

@Sebastiansorry again for the continental topic drift. I do hope we managed to answer your actual question among all these other posts...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

KHorseman
Community Champion
Community Champion

I lied. Obviously you can do it in DAX. I wrote those query formulas almost a year ago when I didn't know nearly as much, and never thought about them again. Of course I could come up with a better way now if I thought about it for a minute.

 

First, add a column that should really be there anyway. Formatted the same way as WeekNumber, but for Month of Year:

 

Month Val = CONCATENATE(YEAR(DateTable[Date]), CONCATENATE(IF(MONTH(DateTable[Date]) < 10, "0", ""), MONTH(DateTable[Date])))

 

Technically you could do this without that column, but I want it to avoid circular dependencies when picking another column as a sort order for Month of Year. Anyway...

 

MonthIndex = CALCULATE(DISTINCTCOUNT(DateTable[Month Val]) + 1, FILTER(ALL(DateTable), DateTable[Month Val] < EARLIER(DateTable[Month Val])))

 

You could substitute Month of Year there instead of Month Val, but then you have to use Month Val as the sort order for Month of Year.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

33 REPLIES 33
Sebastian
Advocate II
Advocate II

Hi all and thanks a lot for the solution.

It is exactly the solution I'm looking for. Great.

 

I was still away and didn't notice that I got so many answers. Sorry for that.

 

 

Thanks a lot @ all.

Greg_Deckler
Super User IV
Super User IV

Perhaps create a measure like:

 

Measure = SUM([Value])

Put the date and this measure in a column chart with date as axis. Drill down to month if necessary, the low bar would be your worst month.

 

Best I can do without more information.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Following this example...

 

You'll need a date table for this if you don't already have one. The date table should also have a column called Month, which is just the name of the month, as well as a Year column. Create a relationship between your table's date column and the date table's date. Then add the following measures (I've named the table you described "SalesTable" for convenience):

 

Sales = SUM(SalesTable[value])

 

Sales on Worst Month = MINX(VALUES(DateTable[Month]), [Sales])

 

Worst Month = IF(
ISBLANK([Sales on Worst Month]),
BLANK(),
CALCULATE(
FIRSTNONBLANK(DateTable[Month], 1),
FILTER(
VALUES(DateTable[Month]),
[Sales] = CALCULATE(
[Sales on Worst Month],
VALUES(DateTable[Month])
)
)
)
)

 

Then stick that Worst Month measure next to the DateTable[Year] and it will give you the name of the month with the worst sum of value.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman Nice solution!

 

I see you switched FIRSTDATE with FIRSTNONBLANK and so I made it work with my YYYY-MMM column and it really works great!

 

I'm about to modify it a bit and implement it for other data too.

 

I had never seen this post! Thanks again! Smiley Happy

KHorseman
Community Champion
Community Champion

Slightly improved version of [Worst Month]. If you use my previous version on a table or matrix visual, it will show up in the Total row at the bottom as the lowest month of any year. That's meaningless, so now here's a version that does not show up in totals at all:

 

Worst Month = IF(HASONEVALUE(DateTable[Year],
IF(
ISBLANK([Sales on Worst Month]),
BLANK(),
CALCULATE(
FIRSTNONBLANK(DateTable[Month], 1),
FILTER(
VALUES(DateTable[Month of Year]),
[Sales] = CALCULATE(
[Sales on Worst Month],
VALUES(DateTable[Month of Year])
)
)
)
),
BLANK()
)





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman The slighly improved version works great too!

 

However if anyone decided to use YYYY-MMM like me (instead of just month) - just use the first formula!

 

When you place in a table/matrix with the YEAR only

 

then the bottom total row will actually show you the all-time best/worst YYYY-MMM and amounts.

 

Here's what I mean...

Best&Worst.png

KHorseman
Community Champion
Community Champion

@Sean yeah, actually when I was first figuring out this little puzzle I was using a MMM-YY column instead, because it was easier to see if my formula was really doing what I thought it was doing. I only switched to Month after I was sure it was giving the right answer for the right reason. You're absolutely right, the total only becomes nonsense when you're only using a month-only column.

 

The SUMMARIZE version should behave the same way; use a "Month of Year" column instead of Month and remove the IF(HASONEVALUE stuff to get the all-time worst in the totals row.

 

As an exercise to the reader, here's a quick puzzle: You can change Alt Worst Month to Alt Best Month by deleting two characters. Which ones?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@KHorseman ",1" after [Monthly Sales] in the TOPN function? This appears to be the optional field to sort the totals by asc order. Removing that will sort the totals in the default descending order making the best month return first. 


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer bingo!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman And to save real estate we can combine the 2 like so...

 

Worst Year-Month & Amount = [Worst Year-Month] & " - " & FORMAT ( [Sales on Worst Year-Month], "Currency" )

@KHorseman Nice! I should just end the day on that high note instead of sticking my head back into my current DAX problem 🙂


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

@Seth_C_Bauer that just makes me wonder what that problem is.

 

@SeanI'm now convinced that there's some way to do a variant of one of these formula patterns that can be placed next to a column of numbers 1-10 that will return in order the top 10 of whatever. Some sort of combo with LASTNONBLANK and RANKX maybe but I haven't managed to make it work reliably yet.

 

This has been a productive thread. I sure hope it answered @Sebastian and we haven't just hijacked him without helping. Smiley Very Happy





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman Yes Smiley Very Happy I'm sure @Sebastian is not expecting this! Smiley Very Happy

 

BTW I still want to run your solution through DAX Studio ( and Wow I just noticed you've added 2 more paragraphs there )

KHorseman
Community Champion
Community Champion

@Sean yeah I decided to comment right after I finished my morning coffee. Dangerous...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman the SUMMARIZE solution works great too and it seems it is in fact faster! (5-7ms faster)

 

I ran both versions in DAX Studio because of the comments I saw on powerpivotpro saying the SUMMARIZE solution is faster

 

And it did actually run faster => 5-7ms faster (on a relatively small data set)

 

One final note => Again for people using YYYY-Month column => skip the IF ( HASONEVALUE ( DateTable[Year] ).....

 

This way you'll get your all-time best/worst in the total row of a table/matix.

 

This has indeed been a very productive thread and hopefully it is now concluded! Smiley Happy

 

Then again @KHorseman will have a chance to respond right after morning coffee... So let see Smiley Very Happy

KHorseman
Community Champion
Community Champion

I did respond right after morning coffee, but not to this thread. Smiley Very Happy

 

That performance comparison is pretty cool @Sean. Maybe this summarize pattern is more useful than I first gave it credit for. I am definitely going to learn how to use DAX studio this week.

 

I still want to find a variant of this that returns the arbitrary n-th best/worst. If you can find the first it must be possible to find the second, surely...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman Yes I saw that response! I should not have mentioned Blank end dates my bad! Smiley LOL

 

Too late now... Smiley LOL 

 

It doesn't really apply to the Holiday Table but the powerivotpro example was for promotions and got me thinking in that direction...

 

Here's a link - explaining some of the features of DAX Studio

Number 4 is Test Performance of your Measures

http://exceleratorbi.com.au/getting-started-dax-studio/

 

It seems you are on a roll today! Smiley LOL

 

KHorseman
Community Champion
Community Champion

@Sean I've cracked it! This might be my favorite formula I've ever written. And I couldn't have figured it out without DAX Studio because nesting two contradictory TOPNs is impossible to keep straight in your mind.

 

OK, so here's the setup: same basic data as this whole thread has been about, plus one extra table called RankTable. That table just has a column named [Rank] which is a continuous series of integers, 1-whatever. 1-10 in my example but you could go as high as you need. That table has no relationships to anything. The desired output is that you drop that column as rows in a table visual or something like that, and you place a measure next to it called [Top Months]. On each row, you get the row-th ranked month by sales. So instead of just giving you the name of the month with the worst sales like we started out with, or the best sales, row 8 gives you the 8th best. You can filter RankTable[Rank] so that it only displays the top 5, or only the 7th, or whatever, and it will always return based on whatever number or numbers are showing in that column.

 

This requires one supplemental measure.

RowTest = FIRSTNONBLANK(RankTable[Rank], 1)

Here's the formula*:

 

Top Months = FIRSTNONBLANK(
	SUMMARIZE(
		TOPN(
			1,
			TOPN(
				[RowTest],
				SUMMARIZE(
					SalesTable,
					DateTable[Month of Year],
					"Monthly Sales",
					SUM(SalesTable[value])
				),
				[Monthly Sales],
				0
			),
			[Monthly Sales],
			1
		),
		[Month of Year]
	),
	1
)

Basically it first does a TOPN of the summarized table based on the number fed to it by the RankTable[Rank] row context. So on row 5 it gives you the top 5 highest sales. Then it does a second TOPN going in the reverse direction, giving you the lowest ranked value of that first TOPN set. For row 1 it only has the 1 highest anyway, but for any row below 1 you want to discard everthing higher than that row. Make sense? Probably not, it's hard to picture. Here, have a test file.

 

 If you want the bottom n results (same thing but counting up from the worst) you would just reverse the 1 and 0 from the two nested TOPNs. So...

 

Bottom Months = FIRSTNONBLANK(
	SUMMARIZE(
		TOPN(
			1,
			TOPN(
				[RowTest],
				SUMMARIZE(
					SalesTable,
					DateTable[Month of Year],
					"Monthly Sales",
					SUM(SalesTable[value])
				),
				[Monthly Sales],
				1
			),
			[Monthly Sales],
			0
		),
		[Month of Year]
	),
	1
)

The example file also has two bonus measures for returning the corresponding sales amounts that produced these top and bottom rankings. Enjoy!

 

 

*Well, almost the formula. I did an extra thing for tie breaking in the real formula but I just wanted to show the basics to make the thing work first. In the example file you'll see that the second outer TOPN is not based on [Monthly Sales]; it's actually on [Monthly Sales] + (1 / [Month Index]). Month Index is an extra column in my date table that is just a unique integer index number. I think this means that in a tie the earlier of the tied months will rank lower, but this formula reverses sort orders so many times that I'm still having trouble keeping it straight.

 

@Sebastiansorry again for the continental topic drift. I do hope we managed to answer your actual question among all these other posts...





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Hi @KHorseman thanks for your solution and the time you have spend for it.

 

How could I create an index in powerbi? is it possible with dax?

KHorseman
Community Champion
Community Champion

@Sebastian glad it works for you.

 

Not sure quite what you mean by index. Do you mean an index column? Like a numeric column to give each row a unique identifier? That's easily done in the Query Editor. There's a button among the Add Column buttons that will add an index column. You can start it at 0, 1, or some custom value.

 

Or are you trying to build the rank table? Just to be clear, that solution will certainly work for you but it's not necessary. If you want a simple solution then the suggestions on the first page of this thread should be more than sufficient for what you originally asked for. That Top Months formula was just something extra I did for fun. But if you do want to use Top Months, I made my RankTable via the Enter Data button. I just typed the numbers into a column and loaded it.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors