Lookup to return a value based on User selection

[waydec]

Hi,

I'm looking for advice on the best way to return a corresponding date/time serial number based on a user-selected date and time selection so that the user can then use the date/time serial number to filter records using an existing table that has a date/time serial column stored as a decimal (eg.  44378.41667 is the serial number for 1 July 2021 10:00 AM).

This is my attempt to get around the lack of a pure date/time filter in Power BI that allows the using to select both date and time using the between option.  Not interested in "relative" dates and times but using the actual date and time in the form of a date/time serial numbers in a from and to filter as a workaround for now given the intended use of the report.

Any ideas and solutions here will be greatly appreciated.  It is a critical issue for us given the nature of our business (emergency services).  A shame that other BI products have this feature but Power BI does not.

Kind regards,

Waydec

[Anonymous · Accepted solution]

Hi @waydec ,

Create a calculated table to show  datetime in decimal format.

FromDateTime = GENERATESERIES(INT(MIN(Data[StartDateTime])), INT(MAX(Data[EndDateTime]))+1, 0.01)

Then create a calculated column to show datetime format.

Measure:

FromDateTime Value = SELECTEDVALUE(FromDateTime[FromDateTime], INT(TODAY())-1)

Calculated column:

SelectedFromDateTime = [FromDateTime Value]

Table looks like as below.

Slicer looks like as below.

For more details you may refer to this blog: How To DataTime Range Slicer

Your demand is a good idea, while it is not supported to implement in Power BI currently.
You can submit a new idea to improve the Power BI.
It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

Best Regards,
Rico Zhou

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

[waydec]

Hi @Anonymous,

Awesome, this works for me.  Thank you very kindly.  I did post the concept as an Idea a while back but no luck so far.

Kind regards,

Waydec