Fill in specific value to all rows in column

[Dimitris_Kats]

Hello dear members,

I have a table with dates and hire date...for example:
Date          Hire Date    
1/1/23         

1/2/23          1/2/23

1/3/23

1/4/23

1/5/23         1/5/23

1/6/23

1/7/23

1/8/23

1/9/23         1/9/23

I want to creat 12 columns (one column for each month) and add the hire dates to the specific columns.

For example if there is a hire date on fabruary then in February column I want to have  the hire date 1/2/23 to all rows:

The desired result is the following:

Date          Hire Date    Janurary   February        March   April    May
1/1/23                                               1/2/23                               1/5/23

1/2/23          1/2/23                          1/2/23                               1/5/23

1/3/23                                               1/2/23                               1/5/23

1/4/23                                               1/2/23                               1/5/23

1/5/23         1/5/23                           1/2/23                               1/5/23

1/6/23                                               1/2/23                               1/5/23          

1/7/23                                               1/2/23                               1/5/23

1/8/23                                               1/2/23                               1/5/23

1/9/23        1/9/23                            1/2/23                               1/5/23

1/10/23                                             1/2/23                               1/5/23

1/11/23                                             1/2/23                               1/5/23

1/12/23                                             1/2/23                               1/5/23

Thank you in advance

[Anonymous · Accepted solution]

Hi @Dimitris_Kats ,

Please refer to my pbix file to see if it helps you.

Create columns.

Jan = CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=1
))

Feb = CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=2
))

Mar = CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=3
))

Apr = CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=4
))

May= CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=5
))

Jun = CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=6
))

Jul = CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=7
))

Aug = CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=8
))

Sep = CALCULATE(MAX('Table'[Hire Date]),FILTER(ALL('Table'),MONTH('Table'[Hire Date])=9
))

If I have misunderstood your meaning, please provide more details with your desired output and your pbix file without privacy information.

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Dimitris_Kats]

Thank you very much @Anonymous that was really helpful!!