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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cparker4486
Helper III
Helper III

Time for me to share some knowledge: Time analysis by Season

Hi everyone,

 

Some of you may know I ask a lot of questions and... have had some difficulty... but I recently did some work in PBI that I'm quite happy with so I thought it would be appropriate to share.

 

I was doing some work on visualizing our electricity usage and wanted to break things down by season. Below are the DAX queries I came up with. (I'd be very happy to learn of a way to improve this.)

 

"Season": This is the column you'd put in your charts.

Season = IF([Day of Year] >= 1 && [Day of Year] <= 60, "Winter",
	IF([Day of Year] >= 61 && [Day of Year] <= 152, "Spring",
		IF([Day of Year] >= 153 && [Day of Year] <= 244, "Summer",
			IF([Day of Year] >= 245 && [Day of Year] <= 335, "Fall",
				IF([Day of Year] >= 336, "Winter", "False")
			)
		)
	)
)

"Day of Year": A numeric value of 1 to 366. [Date] is the date value in your data. (This was needed because FORMAT() is limited in what it can do.)

Day of Year = DATEDIFF(DATE(YEAR([Date]),1,1), [Date],DAY)+1

"SeasonSort": This is so you can display the season list in the correct order. It's very similar to the first query.

SeasonSort = IF([Day of Year] >= 1 && [Day of Year] <= 60, 0,
	IF([Day of Year] >= 61 && [Day of Year] <= 152, 1,
		IF([Day of Year] >= 153 && [Day of Year] <= 244, 2,
			IF([Day of Year] >= 245 && [Day of Year] <= 335, 3,
				IF([Day of Year] >= 336, 0, -1)
			)
		)
	)
)

 

edit: Corrected typo.

3 REPLIES 3
a_mixed_life
Resolver I
Resolver I

Thanks for sharing! This would be helpful for certain industry who does seasonal variant products.

Kris
CahabaData
Memorable Member
Memorable Member

Season - that's a good one - I could foresee needing that...

 

Can you explain the Day of Year?  what was needed versus format() ?

 

 

www.CahabaData.com

As far as I'm aware, FORMAT() doesn't provide the numeric value of day of year. Custom Date and Time Formats for the FORMAT Function (DAX)

 

Making "Day of Year" was faster than using DATE() in the formula for delineating season boundaries.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors