Distribution of Data over 24 Hours & Line Chart

[mohammadyousaf]

Hello.. can someone please help me with the follwoing:

I have the following table. I am trying to distribute the output to on 24 hours, my base is Name. The line chart Axis should be 1-24 hours and value distributed accordingly only during the start and end time. 

Or is there any better way to show the output / hour. Thank you. 

Name	Start Date & Time	End Date & Time	Hours	Out put
M1	11/09/2021 13:10:00	12/09/2021 13:10:00	24	35
M2	11/092021 07:00.00	11/09/2021 09:00:00	2	10
M3	12/09/2021 11:00:00	12/09/2021 15:00:00	4	23

[v-chenwuz-msft · Accepted solution]

Hi @mohammadyousaf,

You can't create a chart unless you create an expanded table based on the table you provided.

Try this measure to create a new tabel.

Table 2 =

VAR TIME_ = GENERATESERIES(TIME(0,0,0),TIME(23,59,59),TIME(1,0,0))

RETURN

SUMMARIZE(

    FILTER(

        CROSSJOIN('Table',TIME_),HOUR([Start Date & Time])<=HOUR([Value])&&HOUR([End Date & Time])>HOUR([Value]) || [Hours]=24

    ),

    [Name],[Value],[Out put],[Hours],"output/hour",[Out put]/[Hours]

)

Then ,create line chart visual and put the [Value] in Axis and [output/hour] in Values.

Best Regards

Community Support Team _ chenwu zhu

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

[mohammadyousaf]

Dear Chenwuz,

Thank you and pardon my skills but can you please tell what do you mean [Value] in the above. 

[v-chenwuz-msft]

Hi @mohammadyousaf ,

The [Value] is the name of column which is created by the DAX expression GENERATESERIES()

Here are the details of GENERATESERIES()

https://docs.microsoft.com/en-us/dax/generateseries-function

And give you my pbix file.

Best Regards

Community Support Team _ chenwu zhu

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