cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerbisingh Frequent Visitor
Frequent Visitor

Need table and Column Chart Display with Time calculation

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.

3 REPLIES 3
Community Support Team
Community Support Team

Re: Need table and Column Chart Display with Time calculation

Hi @powerbisingh ,

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

Assuming that you have the data sample like below.

data sample.PNG

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.

Capture.PNG

 

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
powerbisingh Frequent Visitor
Frequent Visitor

Re: Need table and Column Chart Display with Time calculation

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.

 

FeatureP1 P2Date
WATER16.6970.0001-05-2018 12:01
WATER18.0075.0001-05-2018 02:10
WATER20.0080.0001-05-2018 04:20
WATER24.0085.0001-05-2018 10:00
WATER26.0091.0001-05-2018 13:00
WATER72.2091.9801-05-2018 16:01
WATER82.0099.0001-05-2018 20:44
WATER92.00110.0001-05-2018 23:58
COAL62.0080.0001-05-2018 10:01
COAL50.0060.0001-05-2018 12:01
COAL53.7457.3301-08-2018 14:30
COAL55.0089.0001-08-2018 18:00
COAL57.0099.0001-08-2018 20:00
COAL80.0061.0001-08-2018 21:50
COAL99.0055.0001-08-2018 22:10
COAL74.9980.5201-08-2018 23:50
AIR80.0089.0001-08-2018 01:00
AIR60.0098.0001-08-2018 01:00
AIR85.0080.0001-08-2018 01:00
AIR91.0040.0001-08-2018 01:00
AIR101.0010.0001-08-2018 01:00
AIR52.6621.4001-08-2018 01:00
Community Support Team
Community Support Team

Re: Need table and Column Chart Display with Time calculation

Hi @powerbisingh ,

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 1,315 guests
Please welcome our newest community members: