Forum Discussion
Day function not referencing dates column
I am trying to run a DAX calculation to clean up my dates. I want to create an IF function that checks if a date is on the 1st of the month, and if it is, subtracts 1 day to put it at the end of the previous month. However, I find myself unable to reference my dates column using the day function. Here is the syntax I am trying to use:
Date (Corrected) = CALCULATE(IF(DAY("date")=1,DATEADD("Date",-1,DAY),"date"))
Anonymous write this:
Date (Corrected) = IF(DAY([Date]) = 1, [Date] - 1, [Date])Anonymous You are indeed in the right direction. That's why my intitial tought was to give you the code for the calculated column. You can use what I sent in the first message or create it in Power Query or in the data source.
Please don't forget to accept the relevant message as a solution for community visibility.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas.
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂
6 Replies
Anonymous write this:
Date (Corrected) = IF(DAY([Date]) = 1, [Date] - 1, [Date])Anonymous are you trying to create a measure or a calculated column? If it's a measure , then yes, you need to wrap any column with an aggregate. So, you could use:
Date (Corrected) Measure = VAR _current_date = SELECTEDVALUE('Table'[Date]) RETURN IF(DAY(_current_date) = 1, _current_date - 1, _current_date)