Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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
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?
That's probably just bad syntax on my part, it's probably missing a bracket or has an exra bracket somewhere
Thanks again. When I click on 'show error' it highlights the yearnumber variable name.
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:
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))