Date format

[Anonymous]

I need to format school year, i.e. 2020-21, that I can reference for year-to-year calculations.

[Anonymous · Accepted solution]

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

[PhilipTreacy · Accepted solution]

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

[amitchandak]

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

[Anonymous]

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?

[Anonymous]

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]

I need to convert my date "2020-21" currently formated as text to date for year-to-year calculation purpose. Your suggestion, @Anonymous , did not work.

[Anonymous · Accepted solution]

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]

Thank you, @Anonymous . Does it mean that Power BI has no date format for date ranges that include multiple months?