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 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
Solved! Go to 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
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" )
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
@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))
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
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |