Forum Discussion
Create a date calculation
I need to create an if the else statement based on the following rules and am pulling my hair out.
This should be fairly simple so why I cant fathom it out is beyound me at the moment.
The rule is (assume its 20/02/2020 UK date)
If today is < 26 of the month then "26/02/2020" else add 1 month to make it "26/03/2020"
Obviously need to take into consideration the end of year so assuming its 26/12/2020
then the returned date would be "26/01/2021"
Can someone help me here.
Here is the Power Query code, same logic as the DAX code and the DAX is prettier...
= Table.AddColumn(#"Changed Type", "Custom", each let Year = Date.Year(DateTime.Date([Date])), Month = Date.Month(DateTime.Date([Date])), Day = Date.Day(DateTime.Date([Date])) in if (Day >= 26) and (Month = 12) then #date(Year+1,1,26) else if (Day >= 26) then #date(Year,Month+1,26) else #date(Year,Month,26))
7 Replies
Try this:
Column = VAR __Year = YEAR([Date]) VAR __Month = MONTH([Date]) VAR __Day = DAY([Date]) RETURN SWITCH(TRUE(), __Day>=26 && __Month=12,DATE(__Year+1,1,26), __Day>=26,DATE(__Year,__Month+1,26), DATE(__Year,__Month,26) )Thank you for this,
Is this added as a Custom Column? if so I am getting a "Token Eof expected" error
when I select show error, it highlights __Year
Martin
Yes and I don't get that, attached the PBIX. I did add some spaces between things. I have seen that internationally sometimes having numbers butted up against commas causes problems. If it isn't that, not sure. Seems to work for me.
