How do I make the dates distinct as columns in Pivot Table in Power BI

[aishagarwal]

Hi,

I want the dates to be distinct in the columns of Pivot Table. I removed the timestamp part of the date due to which it is displaying for each record.

Any suggestions appreciated.

Thanks

[Ray_Minds · Accepted solution]

Here we have two options as mentioned in the below :-
Option 1 :- You will need to split the datetime column into two columns ( one for date and another for time) in the power query editor as follows in the below steps.
a). Select the datetime column then click on the home ribbon >>> split column >>>>> by delimeter >>> choose the space condition to split date & time.
b). Now change the data type of each of the columns.
c). Date columns data type should be short date.
d). Apply save and close.

Option 2 :- you will need to create a calculated column based on the existing datetime column as follows:-
a). Click on the new column then write the code below
Date = Format( <datetime column>, "MMDDYYYY").
b). Once the calculated column will create then change the data type as a "short date".
You could use either option 1 or option 2. Both should work perfectly to avoid duplicate date in the matrix visualisations.

Please feel free to let me know if you have any questions.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[aishagarwal]

[Audit Trail Date] is the datetime field 

[ryan_mayu]

if the issue has not been fixed, you can provide your pbix file for further investigation.

[danextian]

That formula simply references the original column and doesn't parse just the date component of it. If you format that to decimal,  you will see that there still are decimal values.

[aishagarwal]

Hi @ryan_mayu,

Yes, I created a date column from the datetime field and have used it here. No difference.

Thanks

[danextian]

Hi @aishagarwal 

Creatinig a date column from a datetime field shouldn't result to duplicate row or column values in a visual.  However, simply formatting that datetime column to a date format will return duplicate values as shown in the screenshot below.

Goin back to @ryan_mayu 's suggestion, what formula did you use to create a date column? In the screenshot below, I created one using the INT function which simply returns the integer component of a datetime field (which is a whole number and a decimal) and format it to date.  You will see that the dates no longer show  duplicates.

[ryan_mayu]

how did you create the date column? It should work. pls see the attachment below

[ryan_mayu]

Is that acual a date type or datetime? if there is a time value which is hidden, i think you need to create a date only column and add that column to the matrix visual.