Forum Discussion
Date format
I need to format school year, i.e. 2020-21, that I can reference for year-to-year calculations.
Hi Anonymous,
Sure, your values aren't the regular date types, it seems like you stored the start and end years into one field, right?
If this is a case, I'd like to suggest you add two calculated columns to extract the start part and end part from your raw field then you can set conditions based on new fields.
Calculate columns:
Start = DATE ( VALUE ( LEFT ( [Year], 4 ) ), 12, 31 ) End = DATE ( VALUE ( LEFT ( [Start], 2 ) & RIGHT ( [Year], 2 ) ), 12, 31 )Notice: the right part 12 and 31 is the default value I used to complete the date function because your raw value did not exist the month and day levels.
Regards,
Xiaoxin Sheng
Anonymous It has a date or datetime data type depending on whether you are using Power Query or DAX, but not sure what youo mean by date format for date ranges - usually when you want to work with elapsed time or durations you have a separate start and end date/time.
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
7 Replies
Anonymous ,Not clear without sample data
Way on - if all dates are in same year
Year = Year([Date]) & "-" & (Year([Date]) +1)
Start Year = STARTOFYEAR('Date'[Date],"3/31")
End Year = ENDOFYEAR('Date'[Date],"3/31")
Year = year([Start Year]) & "-" & year([End Year])
I am sorry I am a Power BI begginer, my dates (YYYY-YY or 2020-21) can only be formatted as Text, thus I am unable to use year-to-year, calculations. Should there be a way to enter your codes amitchandak, (Year = year([Start Year]) & "-" & year([End Year])), where do I write it?
HI Anonymous,
What type of effect are you wanted? Change the display formula and keeping raw data values or only convert them to a specific format?
For the first scenario, you can set 'custom' field string in table property, it will change the display string without makeup on your field values.
Use custom format strings in Power BI Desktop
If you use the format function as amitchandak mentioned, it will force convert the result as text type. (second scenario)
Regards,
Xiaoxin Sheng
Anonymous It has a date or datetime data type depending on whether you are using Power Query or DAX, but not sure what youo mean by date format for date ranges - usually when you want to work with elapsed time or durations you have a separate start and end date/time.
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
