@sienna28
Nope, not too complex at all, but getting the requirements is key to providing the solution.
Your Items property for your ColumnChart should be:
With({_year: 2021},
With({_items:
AddColumns(
Filter(yourDataSource, OrderCompletedDate >= Date(_year, 1, 1) && OrderCompletedDate<= Date(_year,12, 31)),
"_month", Month(OrderCompletedDate)
)
},
ForAll(Sequence(12),
{_month: Last(FirstN(Calendar.MonthsShort(), Value).Value,
_count: CountRows(Filter(_items, _month=Value))
}
)
)
)
Replace the name of the data source to yours.
Set your Labels property of the chart to : _month and the Series1 property to : _count
and you will then have your chart.
If you want to add some sort of option for selecting a year, you could consider a dropdown (let's call it ddYear) with an Items property of : ForAll(Sequence(3, 0), Year(Today())-Value)
This would give you the current and past two years in the dropdown.
You can then replace the hard-written 2021 in the formula with ddYear.Selected.Value
Note that the above does not use the Year and Month columns you already have in the data list as they are not needed and can derive from the OrderCompletedDate itself. However, adding the variable year span in the formula will provide delegation warnings. If your datasource is less than 2000 items (and not expected to go beyond) then you can ignore the warning. If it is, then you can adjust the formula to utilize the Year that you already have in the record instead.
