Using a measure formatted as a date as an x-axis

[pb123]

I have a measure that works out an ETA date of an item, based on the lat/lon of an item, the speed of the item. the lat/lon of the destination, and the speed of the item. I also have a parameter that lets me increase/decrease the speed and see the impact on the ETA.

This system seems to work properly, and I get a list of ETAs for each item which changes with the speed adjustment.

However, I want to be able to graph out the count of items by ETA date, using date as the x axis, while still being able to use the what-if parameter.

I have tried to recreate the measure as a column, but then I can't use the what-if parameter because the eta needs to be a measure.

Any ideas how to solve this problem? In my mind the ETA measure needs to be related to my date table in some way. 

Thanks!

[AndrewDavies437]

Hi,

You'll need a date table to do this. Here's some code to make you one:

Date =
--This is a date table. Switch the tables, set as variables below, to your minimum date and maximum date.

VAR MinYear = YEAR ( MIN ( INSERT DATE COLUMN HERE ) )
VAR MaxYear = YEAR ( MAX ( INSERT DATE COLUMN HERE ) )

RETURN

ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),

"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Weekday", FORMAT ( [Date], "dddd" ),
"Weekday number", WEEKDAY( [Date] ),
"Quarter Number", QUARTER( [Date] )
)

This will make you a date table if you go to Data tab and then "New Table" and paste this code in (make sure to remember to insert your date column at the top). Once you've done that, go to the model tab and create a relationship between Date[Date] and your date in your model. Once you've done this just put the date from this table as your axis.

Hope this helped pal

[pb123]

I already have a date table, which I've been using.

[pb123]

@amitchandak Thanks, but I can't seem to get this to work. Does it matter that my independent table and measure are in date format?

[amitchandak]

@pb123 , You can not use measure on axis. You need to create an independent date table and join with this measure in new measures using  a grouping level

refer

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ