Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Creating a date column with only day and month

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,
1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

6 REPLIES 6
durack99
Advocate I
Advocate I

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. 

durack99_0-1666146781944.png

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
Frequent Visitor

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

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
Not applicable

@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
Not applicable

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
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel1

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.