How to Aggregate Data Per Day, Week, Month, Year in Line Chart

[Ibrahim_shaik]

Hi Power BI Community,

I have a table with Date Column which has date, month, year in it like Thursday, March07, 2024 and  a time column with a 5 minute time interval timestamp for 24 hours and I have numerical columns. this is connected database via import mode and data keeps updating.

I need urgent help in Creating Line Chart

I want to show the numerical columns data on the Line Chart.

If I Selected a 1 Day the Line chart should show Hourly data of 24 hours.

if I Selected 2 Days the line chart should show 24 hour timestamps 

If I selected more than two days it should show daily and if i selected week it should week same for month and year as well.

Please give a Solution.

[Anonymous · Accepted solution]

Hi  @Ibrahim_shaik ,

@lbendlin , thanks for your concern about this case. I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

Here are the steps you can follow：

1. Create calculated column.

Week = WEEKNUM('Table'[Date],1)

Month =
MONTH('Table'[Date])

Year = YEAR('Table'[Date])

Date_format =
 DATE(
    YEAR([Date]),MONTH([Date]),DAY([Date]))

2. Create calculated table -- Slicer.

Table 2 =
DISTINCT('Table'[Date_format])

3. Modeling – New parameter –Fields.

4. Create measure.

Flag =
var _date=
SELECTCOLUMNS('Table 2',"date",'Table 2'[Date_format])
var _week=
SELECTCOLUMNS(FILTER(ALL('Table 2'),'Table 2'[Date_format] in _date),"week",WEEKNUM('Table 2'[Date_format],2))
var _count=
COUNTX(ALLSELECTED('Table 2'),'Table 2'[Date_format])
var _select=SELECTEDVALUE('Parameter'[Parameter Fields])
return
SWITCH(
    TRUE(),
  CONTAINSSTRING(_select,"Date")=TRUE()&&_count<=2 &&MAX('Table'[Date_format]) in _date,1,
  CONTAINSSTRING(_select,"Week")=TRUE()&&WEEKNUM(MAX('Table'[Date]),2) in _week ,1,0)

5. Treat Parameter[Parameter] as the x-axis of the slicer and the line graph, respectively.

6. Place [Flag]in Filters, set is=1, apply filter.

7. Result:

Slicer Date_format :select tow dates Slicer Parameter: select week.

Click Drill Down on the visual:

Slicer Date_format :select multiple dates Slicer Parameter: select week.

Click Drill Down on the visual:

Best Regards,

Liu Yang

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

[lbendlin]

Add these fields one by one into the x axis hierarchy, and teach your users how to navigate up and dowm hierarchies.

Expect Power BI to enforce sampling of data, so the UX may be sub-optimal.

Also consider using zoom sliders on the x axis.

[Ibrahim_shaik]

Thank you for the response.

How to use zoom slider on x axis?

[lbendlin]

[Ibrahim_shaik]

But for my chart there is no option to apply zoom slider on X-axis.

I have tried using the drill down but the data not aggregating to Hour. I have 5 minute interval timestamps 00:01:01, 00:05:01 like this, per day its aggregating when i drill up.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...