SOLVED

Pivot chart

Copper Contributor

I am creating a pivot chart from a master sheet of data. The column I am using has 20 characters, I need to only show the first 10 characters. I believe I need to add a measure but I can't get my DAX formula to work. 

Example, my data is 2024-06-03T12:00:07.865Z and I need my pivot chart to look at the 2024-06-03 portion of that data. 

I have created a measure with a left formula but it isn't working. 

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Rnkoerner Why DAX? Connect to the data with Power Query, Add a column that extracts the first 10 characters. Set the data type to Date, load to the Data Model and use that 'date-only' column in the pivot table/chart. 

 

 

@Rnkoerner 

I guess you use that field as label, not as value. In data model you may add calculating column (not measure) as

=DATEVALUE( LEFT( Table1[Date], 10 ) )

and work with it. In any case you need to transform it from text to date.

Or do the same with Power Query as @Riny_van_Eekelen suggested.

 

 

@Riny_van_Eekelen thank you so much!!

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Rnkoerner Why DAX? Connect to the data with Power Query, Add a column that extracts the first 10 characters. Set the data type to Date, load to the Data Model and use that 'date-only' column in the pivot table/chart. 

 

 

View solution in original post