Line chart with (custom) date range

[SofticKabir]

Hello,

I want to create a chart which would then display the amount of cases of a person between 01. January and 31. December 2021.

When a user creates a report, his Fullname is stored as well as the date of creation.

For example if there are 5 reports that were created on the same day, the chart would display 5 bars of different color for the same date.

It would look like something like this (without displaying the time, but only the date in "dd/mm/yyyy" format) :

How can one achieve this?

Best regards,

Softic Kabir

[rubin_boer]

hi @SofticKabir consider adding a column with the format as you wish like

Items = AddColumns(YourDataSource,"myDate",Text(YourDateColumn,"[$-en-US]dd/mm/yyy")) 

Set the Labels to myDate

hope this helps

[SofticKabir]

Hi @rubin_boer ,

thank you for the suggestion. I applied that but there are still some things that are not correct.

For example I want to display the date (from left to right on the x-axis) 01/01/2021 until 31.12.2021. And the line should be on the days where there were cases that day. Y-axis is then the amount of the cases.

For the moment it looks like this (after having applied your suggested change):

[rubin_boer]

hi @SofticKabir I have recreated this on my side:

5 entries with 2 being on the same date with different times

the above is a collection where i added the column with the formatted date (no timestamp)

ClearCollect(colChartData,AddColumns(YourDataSource,"myDate",Text(YourDateColumn,"[$-en-US]dd/mm/yyyy")));

Now i added the chart and sorted the data in the chart by the "myDate" column I added above as well as group the data by the myDate as you want to have a count as to how many entries on that day

Sort(AddColumns(GroupBy(colChartData,"myDate","Grouped Date"),"Vals",CountRows('Grouped Date')),myDate,Ascending)

the chart is now sorted by date and show the total of entries by date (26/4/2021 had 2 entries)

the series include the values (counts of entries) by new date columns

i hope this helps

[SofticKabir]

Hey @rubin_boer ,

I tried to apply your suggestion again but something is wrong in the code. I am not sure what exactly but this is what I have:

Sort(AddColumns(GroupBy(Table1.Datum;"_myDate";"GroupedDate");"Vals";CountRows(GroupedDate));_myDate;Ascending)

It says "The function "Sort" has invalid arguments" and "The function "GroupBy" has invalid arguments"

[rubin_boer]

@SofticKabir can you share a picture of your data (column names and data).

Sort(AddColumns(GroupBy(Table1;"Datum";"GroupedDate");"Vals";CountRows(GroupedDate));Datum;Ascending)

Can you also try the change i made above.

[SofticKabir]

I tried that code, it shows no error anymore but the result is still not correct 🙂

Here is a screenshot of my colum "Datum". And a screenshot of my column "Erstellt von" which translates into "created by".

[rubin_boer]

hi @SofticKabir i used your headers and created this. 

Can you please draw a graph in excel to show what you are looking for. the graphs in PowerApps can't be layered/stacked

The table is like yours (Table 18)

It is then grouped by days to get the count for that day (AddColumns(GroupBy(Table18,"Datum","Grouped"),"Total",CountRows(Grouped)))

both charts have the grouped data as Items.

[SofticKabir]

Hi @rubin_boer 

This seems to work. It displays the number of cases for each day accordingly.

But, I noticed something strange. My "Table1" is a spreadsheet which is stored over OneDrive. I am also using Data Connection of OneDrive, so normally the data in the app should be the same as in the table. Somehow, the dates aren't the same, for example in the app I have 3 cases:

04.04.2021	John Doe
01.03.2021	Kabir Softic
01.03.2021	Kabir Softic

These dates look different in my spreadsheet:

Do you know why this might be like this?

[SofticKabir]

The problem still persists...