Day function not referencing dates column

[Anonymous]

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"))

[SpartaBI · Accepted solution]

@Anonymous write this:

Date (Corrected) = 
IF(DAY([Date]) = 1, [Date] - 1, [Date])

      

Showcase Report – Contoso By SpartaBI

[SpartaBI · Accepted solution]

@Anonymous write this:

Date (Corrected) = 
IF(DAY([Date]) = 1, [Date] - 1, [Date])

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

The problem is that the Day function isn't recognizing the name of my dates column. Unless i enclose it with a sum function first Day(Sum("date")). It only allows me to select calculated measures.

[SpartaBI]

@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)

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

Yes I was building a measure and your formula worked! I realized however that I cannot use a measure as a primary key in a relationship to another dataset. I imagine I should actually be using a calculated column in the "Transform Data" editor, is that right?

The problem is that most of my dates in the original dataset are correctly entered as end of month, but there are 3 or 4 each month that are entered as the 1st of the following month. Hence my need to clean the data so that my visuals are accurate. I hope this makes sense. Really appreciate the help.

[SpartaBI · Accepted solution]

@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 🙂

[Anonymous]

Yes I was building a measure and your formula worked! I realized however that I cannot use a measure as a primary key in a relationship to another dataset. I imagine I should actually be using a calculated column in the "Transform Data" editor, is that right?

The problem is that most of my dates in the original dataset are correctly entered as end of month, but there are 3 or 4 each month that are entered as the 1st of the following month. Hence my need to clean the data so that my visuals are accurate. I hope this makes sense. Really appreciate the help.