Changing date to one day per month

[thatjoey20]

Hi guys, 

I have a date table like below: (this is on dd/mm/yyyy format)

that has many days in a single month, normal, but is there a way to change this column like to be one day per month? For instance: 

01/01/2019
01/02/2019 
01/03/2019 and so on..

I want this because I have a value column based on month, but it sums the value since I have different days in a month.

I problaby bring a simple problem, but if any of you can give me a hint or another way to solve it would be very nice

[Singaravelu_R]

Create seperate calendar table by using below DAX and related with date column of your table with calendar table date (one to many relationship)

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000;1;1); DATE(2025;12;31));
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
"Year"; YEAR ( [Date] );
"Monthnumber"; FORMAT ( [Date]; "MM" );
"YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
"YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
"MonthNameShort"; FORMAT ( [Date]; "mmm" );
"MonthNameLong"; FORMAT ( [Date]; "mmmm" );
"DayOfWeekNumber"; WEEKDAY ( [Date] );
"DayOfWeek"; FORMAT ( [Date]; "dddd" );
"DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
"Quarter"; "Q" & FORMAT ( [Date]; "Q" );
"YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )

[v-jayw-msft · Accepted solution]

Hi @thatjoey20 ,

You could create a calendar table and create a relationship between this table and calendar table like below.

calendar = CALENDARAUTO()

Best Regard,

Jay

[amitchandak]

@thatjoey20 , if dd/mm/yyyy has been take as date then it because of system setting and will not change mm/dd/yyyy without setting change https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

You can create new date columns from formats

new date(US to Uk) =
var _pos = search("/",[Date],,0)
return
date(right([date],4), left([date],_pos-1), mid([date],_pos+1,2))

new date(UK to US) =
var _pos = search("/",[col],,0)
return
date(right([date],4), mid([date],_pos+1,2), left([date],_pos-1))

[thatjoey20]

the format for me it's okay, we use this format here, the real problem is that I want to take out the many days and set only to be one day per month like 1st may of 2019, 1st june of 2019 , 1st july of 2019