Need table and Column Chart Display with Time calculation

[Anonymous]

Hi All,

My requirement is calculation with time.

I have 2 fields in data one is date with Time: 01-05-2019 12:01, 01:00,4:00 and so on and Power Generated 100KW, 200 kw, 300 kw and so on. The requirement is 2 filters needs to be applied one is date and second one is time. 1st date should be selected then all the time values appear in slicer , If I select any time data should display till that time of the day for example if i Select 01-05-2019  and then 10AM. I should display all the  records starting from 12:00 AM till 10AM. Can any one suggest the right code or option to get the output on to a chart and table. Thanks.

[v-piga-msft]

Hi @Anonymous ,

For your requirement, I have made a sample to reproduce your scenario.

Assuming that you have the data sample like below.

Please refer to the steps below.

1. Split the column Datetime to get the date and time column.

2. Go to Data view and create a calculated table with the formula below.

Time = SUMMARIZE('Table','Table'[Date],'Table'[Time])

3. Create the measure with the following expression.

Measure =
VAR a =
    SELECTEDVALUE ( 'Time'[Time] )
VAR adate =
    SELECTEDVALUE ( 'Time'[Date] )
VAR b =
    MAX ( 'Table'[Time] )
VAR bdate =
    SELECTEDVALUE ( 'Table'[Date] )
RETURN
    IF ( adate = bdate && b <= a, 1, 0 )

4. You could create the date slicer and the time slicer with the column from Time table.

5. Create the table visual and drag the measure in visual level filter and show value as 1 like below.

More details, please refer to my attachment.

If you still need help, please share your data sample and your desired output so that we could help further on it.

Best Regards,

Cherry

[Anonymous]

Hi Cherry, Thanks for the reply. This solution  is solving the problem partially. Exact requirement is I have attached sheet with data. It has 4 fileds viz FEATURE, P1, P2 and Date with timestamp. I need 2 outputs one is a table as shared a solution by you that is alright, another one is a graph which shows the same data in Line Chart with individual lines. Below is the sample data. Thanks.

Feature	P1	P2	Date
WATER	16.69	70.00	01-05-2018 12:01
WATER	18.00	75.00	01-05-2018 02:10
WATER	20.00	80.00	01-05-2018 04:20
WATER	24.00	85.00	01-05-2018 10:00
WATER	26.00	91.00	01-05-2018 13:00
WATER	72.20	91.98	01-05-2018 16:01
WATER	82.00	99.00	01-05-2018 20:44
WATER	92.00	110.00	01-05-2018 23:58
COAL	62.00	80.00	01-05-2018 10:01
COAL	50.00	60.00	01-05-2018 12:01
COAL	53.74	57.33	01-08-2018 14:30
COAL	55.00	89.00	01-08-2018 18:00
COAL	57.00	99.00	01-08-2018 20:00
COAL	80.00	61.00	01-08-2018 21:50
COAL	99.00	55.00	01-08-2018 22:10
COAL	74.99	80.52	01-08-2018 23:50
AIR	80.00	89.00	01-08-2018 01:00
AIR	60.00	98.00	01-08-2018 01:00
AIR	85.00	80.00	01-08-2018 01:00
AIR	91.00	40.00	01-08-2018 01:00
AIR	101.00	10.00	01-08-2018 01:00
AIR	52.66	21.40	01-08-2018 01:00

[v-piga-msft]

Hi @Anonymous ,

Sorry for the delay.

It seems that you want to create a line chart.

I have a little confused about what is your desired line chart.

As I have solved your problem that calculate the time, you should create the line chart based on your requirement.

It seems that anohter requirement, you'd better create a new topic so that people who have the same problem will find the solution directly.

Best  Regards,

Cherry