Custom Time Period Names

[seanmackaay]

Hi

Relatively new to Power BI and my DAX skills are pretty basic. I've got a list of dates and need to come up with a calculated column that does the following:

1. If a date is between 1 December and 30 April then the column should have 'WinterYYYY', for instance between 1 December 2018 and 30 April 2019 it should be 'Winter1819', between 1 December 2019 and 30 April 2020 it should be Winter 1920, etc

2. If the date is between 1 May and 30 November then it should be 'Summer2019' (for this year, or if those dates are in 2020 then it would be 'Summer2020', etc).

For the life of me I can't come up with a way of doing this but as I say my DAX skills are pretty basic.

Thanks for any help anyone can offer.

[jthomson · Accepted solution]

Try something like:

newcolumn = 

var yearnumber = mod(year[date],100)

var decembernumber = concatenate(yearnumber,yearnumber+1)

var jantoaprnumber = concatentate(yearnumber-1,yearnumber)

var summernumber = concatenate(yearnumber,yearnumber)

return if (month([date])=12,concatenate("Winter",decembernumber),if(month([date])<4,concatenate("Winter",jantoaprnumber),concatenate("Summer",summernumber))

First bit is basically defining the YYYY values, the second bit is looking at your month and then merging summer/winter to the YYYY value as appropriate

[seanmackaay]

Thanks. The logic in that makes sense to me but I'm getting a 'Token Eof expected' error when I'm trying to do it. I've got one date column called 'Date' that has the format of dd/mm/yyyy, would that be causing the issue?

[jthomson]

That's probably just bad syntax on my part, it's probably missing a bracket or has an exra bracket somewhere

[seanmackaay]

Thanks again. When I click on 'show error' it highlights the yearnumber variable name.

[seanmackaay]

Managed to get this to work (a typo and a call to a table that didn't exist). But am getting very strange values now. So for instance 20/11/2012 comes out as Summer3333. See screenshot below:

[seanmackaay]

Managed to essentially get it working using the following:

newcolumn = 

var yearnumber = year[date].[year]

var decembernumber = concatenate(yearnumber,yearnumber+1)

var jantoaprnumber = concatentate(yearnumber-1,yearnumber)

var summernumber = yearnumber

return if (month([date])=12,concatenate("Winter",decembernumber),if(month([date])<4,concatenate("Winter",jantoaprnumber),concatenate("Summer",summernumber))