topic Re: How to Aggregate Data Per Day, Week, Month, Year in Line Chart in Developer

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

Ibrahim_shaik — Thu, 07 Mar 2024 12:46:11 GMT

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.

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

lbendlin — Thu, 07 Mar 2024 14:08:54 GMT

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.

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

Ibrahim_shaik — Thu, 07 Mar 2024 15:38:28 GMT

Thank you for the response.

How to use zoom slider on x axis?

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

lbendlin — Thu, 07 Mar 2024 15:47:56 GMT

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

Ibrahim_shaik — Fri, 08 Mar 2024 10:58:40 GMT

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.

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

lbendlin — Fri, 08 Mar 2024 14:49:25 GMT

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-Forum/ba-p/963216

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/1447523

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

Anonymous — Wed, 27 Mar 2024 08:18:01 GMT

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