Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |