Hi @Elitezone ,
Do you want to use a Line-Chart to shown the cumulate records in a period or from the beginning? How many records are there in your SP list? How long would you like the period to be?
I assume that you want to display the whole records cumulated from the beginning till now. I made a test on my side.
I put below formulas on the Chart Screen’s OnVisible:
ClearCollect(
RecordCount,
DropColumns(
AddColumns(
GroupBy(
AddColumns(
SortByColumns(
ListName,
"Created",
Ascending
),
"dates",
Text(
ThisRecord.Created,
DateTimeFormat.ShortDate
)
),
"dates",
"GroupDate"
),
"counts",
CountRows(GroupDate)
),
"GroupDate"
)
);
Clear(ttRecord);
ForAll(
RecordCount,
Collect(
ttRecord,
Patch(
Last(
FirstN(
RecordCount,
CountRows(ttRecord) + 1
)
),
{
ttCount: Sum(
ttRecord,
counts
)
}
)
)
);
ClearCollect(
ttCounts,
DropColumns(
AddColumns(
ttRecord,
"tCount",
Sum(
counts,
ttCount
)
),
"ttCount",
"counts"
)
)
Let me explain the formulas.
First I created a collection “RecordCount”, with the columns of dates and number of records belonging to each date. I sort the data source by the column so the records sort ascending of dates and formatted the “Created” column to a date only format “DateTimeFormat.ShortDate” in the “dates” column, then group by “dates” column to get a table with a column “counts” to CountRows of the grouped column “GroupDate”, then drop the colomns to get a table only contains the dates and number or records in each date.
Next step I created a new collection “ttRecord” to add a column “ttCount” to retrieve the result of each record’s “count” plus the previous record’s.
But since the column “ttCount” with fields in wrong places (one line down of the proper), I have to create a third collection to sum each records “counts” and “ttCount” field. At last, using DropColumns to modify the collection into a table with two columns “dates” and “tCount”.
Below is my test chart screen, the Line-Chart's Items property is set to ttCounts.
Since a large amount of counting would trigger when the screen shows up (records lists are mostly large data sets), I assume there would be a bad performance. The best way is using Power BI but I wonder is there any other method such as with the SP list “Use calculated value” function?
Hope this helps.
Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.
