Creating a date column with only day and month

[Anonymous]

Hi everyone,

I need to create a Data filter that shows me the values between, for example 1/09 to 20/11, of many years (i.e. 2017-2018-2019) all based on that period. How can I do it? Is it possible to create a date column that does not take into consideration the year and only report the day and month ?

Thanks for helping me,

[durack99]

Another solution perhaps is to:

1. Put the date value (in date form not string) into the matrix/table/other visual

2. Select the measure/column in the fields pane

3. Under the format pane at the top of the page, you should be able to enter custom formats that will still interpret the date as a date rather than a string, however you can customise it more than the options shown on the drop down. 

In this case if you wanted the day of the month and then the month name, e.g. 1 January, you would put in this format.

[Adsy]

I was having the same issue, and this is a big help. Thank you! 😍

[WillDrotar]

Thank you for posting this. I don't know if this is a new feature, but this literally saves us in DirectQuery mode needing to create the dumb date hierarchy. If we have 100 date fields, creating 400 helper columns to hold in memory/increase model size is crazy. Great find!

[Anonymous]

@Anonymous there are ways you can achieve this

Either you can use format function FORMAT('Table'[Date],"dd/MM")
or you can use CONCATENATE(DAY('Table'[Date]),CONCATENATE("/",MONTH('Table'[Date]))

[Anonymous]

Thanks, but this solution will give me a text format column, I need a date format because in this way the filter recognize the value as a date and let me filter between period. With a text format I have to select from the filter each day, and for a long period of month is a long process.

[az38 · Accepted solution]

Hi @Anonymous it depends on user behaviour in your report.

but maybe the good solution will beto create date hierarchy which includes only Day and Month and try to work with it

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin