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

ah, ok.

 

@KHorseman

i meaned the column "Monthindex" in your DateTable. You used it for the calculation of the bottom months.

KHorseman
Community Champion
Community Champion

Oh yeah. That part should actually be in all of those formulas. Top Months, Bottom Months, Top Sales, Bottom Sales... I'm just using it as a forced tiebreaker in case two months ever match exactly. It's optional. I think if you skip that part of the pattern it will basically just choose randomly if any two months tie.*

 

Anyway the formula for it is in the query for DateTable in that example file I linked previously. Go to Edit Queries and it's the 12th step. You're welcome to steal the entire query if you like. The source step is where you set the start date and the total number of dates covered by the table to whatever suits your needs. Just open the advanced editor in the query and copy and paste the whole thing into your own blank query. But if you just want to add a month index to your own query the formula is:

 

=if [Index] = 1 then 1
else if [Date] = Date.StartOfMonth([Date]) then

  List.Count( List.Distinct( List.FirstN( #"Added Month of Year"[Month of Year], [Index] - 1))) + 1
else List.Count( List.Distinct( List.FirstN( #"Added Month of Year"[Month of Year], [Index] - 1)))

 

...which in English translates to, "Put a 1 on the first row. After that, check to see if the date is the start of a new month. If it is, add 1 to the value in the previous row. If not just copy the previous row." [Index] is just a regular index column starting at 1 as I described in my last reply. I don't know how to do the equivalent in DAX, or even if it's possible in DAX. Of course you can do it in DAX. Duh. See the next post in this thread.

 

* for bonus points, consider how the basic pattern sorts things. You could either add or subtract 1/[MonthIndex] as a tiebreaker value. Depending on whether you add or subtract, it will mean that earlier months rank either higher or lower. To be consistent you may need to perform the opposite operation on Top Months than you do on Bottom Months, because one sorts by how low the value is and the other sorts by how high it is.





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

Proud to be a Super User!




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

@KHorseman thanks a lot. Exactly what i'm looking for.

 

Great man.

Sean
Community Champion
Community Champion

@KHorseman I just implemented it and IT WORKS!!!

KHorseman
Community Champion
Community Champion

@Sean re-download the example file. I just uploaded a new version with extra stuff a minute ago.





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

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman EDIT: Nevermind!

KHorseman
Community Champion
Community Champion

I'm not sure I have enough context to answer, but possibly the problem is that the setup on the left has years as row context? Table visuals sometimes seem to get weird when you try to sort them by a measure. If you have more than one naked column showing on the table it will just sort within each outer row context. Try filtering that table on the left to only show 2012. The overall top 4 are all in that year so with that filter you should get matching results for those 4. If so, it's just table visuals being table visuals. If not, something really is wrong.

 

Edit: we have got to stop editing so much. We both talk over each other's edits. 😛





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

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman Yes! Sorry about that... Smiley Happy

 

My table on the left is within each YEAR Best (Previous Measure) and the New Measure ranks top 5 best of all time! 

Obviously they can be diffrent! Smiley Happy

Rank Top 5 - 2png.png

 

EDIT: Again @KHorseman really great solution!

Here's my version... maybe slightly easier to read - DESC instead of 0 and ASC instead of 1 and spelled out the Row Test Measure

Great job! Smiley Happy

Top Months Z =
FIRSTNONBLANK (
    SUMMARIZE (
        TOPN (
            1,
            TOPN (
                MIN ( RankTable[Rank Column] ),
                SUMMARIZE (
                    SalesTable,
                    'Calendar'[Year-Month],
                    "Monthly Sales", SUM ( SalesTable[Value] )
                ),
                [Monthly Sales], DESC
            ),
            [Monthly Sales], ASC
        ),
        [Year-Month]
    ),
    1
)
KHorseman
Community Champion
Community Champion

@Sean well I did say we both do it. Smiley Wink

 

This pattern is pretty flexible. If you want years, switch the visual to a matrix. Drop Year and Rank into the rows well and you'll get Top Months and Bottom Months and whatever else per-year with no adjustments to the formulas.

 

TopBottomYears.PNG

 

Edit: yeah that rewrite is much more readable. Good work to you too.





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

Proud to be a Super User!




Sean
Community Champion
Community Champion

@KHorseman we are already on Page 3 and still no word from @Sebastian Smiley LOL

Sean
Community Champion
Community Champion

@KHorseman I will disqualify myself from the quiz since I've already done it Smiley Happy

But yes the change from one to the other comes to exactly this!

 

Have not tried the SUMMARIZE solution yet but I'll give a go later today... Smiley Happy

KHorseman
Community Champion
Community Champion

And just because I'm trying to learn how the SUMMARIZE() function works*, here's a kind of goofy method of getting the same results. For this one you don't need any other supplemental measures, but you do still need the date table.

 

Alt Worst Month = IF(
HASONEVALUE(DateTable[Year]),
FIRSTNONBLANK(
SUMMARIZE(
TOPN(
1,
SUMMARIZE(
SalesTable,
DateTable[Month],
"Monthly Sales",
SUM(SalesTable[value])
),
[Monthly Sales],
1
),
[Month]
),
1
),
BLANK()
)

 

*and because it's Friday and this is the sort of thing I consider slacking off at work... Smiley Tongue





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

Top Solution Authors
Top Kudoed Authors