Monthly average of a daily sum

[Amit_B]

Hey,

I have a table that contains all the orders in each day. Each row (key of date & order) has additional data such as transaction type, customer type, etc.

At first,
I want to create a measure that will count the orders each day, then it will calculate the daily average for each month.
For example: 4 orders on 1/1, 5 orders on 1/2 and 6 orders on 1/3, so the monthly average is 5.
I tried to do something like this but it doesn't give me correct result:
Avg(Aggr(Count(OrderID), Date))

Second,
In the case that the user's selection include one month only -
For example:
* If only January is selected but no year is selected then there are as many months as the number of years.
* If the user only selects the current year and we are in January then there is one month.
In this case, I want the current month and the previous 5 months to be displayed.
For example, if the user selected April 2024, then we will see the data from November 2023 to April 2024.
I created two variables for this. The first if the period start date and the second is the period end date. They get the correct values ​​but when selecting a month I still see only the selected month.
This is what I Tried but even the first issue didn't work:
Avg(Aggr(Count({<Date={">=$(=vStartDate)<=$(=vEndDate)"}>}OrderID),Date))
or:
Avg(Aggr(Count({<Date={">=$(=vStartDate)<=$(=vEndDate)"}, Year={$(=Max(Year)),$(=Max(Year)-1)}, Year=, Quarter=, Month=>}OrderID),Date))

I should use it in Bar Chart - Year-Month on X axis, this measure on Y axis.
Hope I was clear.
Please help with this 2 problems.

Thanks,
Amir.

Dateset:

Date (YYYY-MM-DD)	OrderID	[more fields..]
2024-01-01	00001
2024-01-01	00002
2024-02-01	00001
2024-02-01	00002
2024-02-02	00001
2024-02-02	00002
2024-02-02	00003
2024-03-01	00001
2024-03-01	00002
2024-03-01	00003
2024-03-01	00004
2024-03-01	00005

[Vegar]

Avg(Aggr(Count(OrderID), Date)): do you need the aggr? What if you use count the number of orderIDs and divide that with the number of days? =Count(Distinct OrderID)/Count(Distinct Date)

{<Date={">=$(=vStartDate)<=$(=vEndDate)"}>}: My best guess is that your selections on month (and  year) will limit the scope of data. Try ignoring selections in those fields like this: {<Date={">=$(=vStartDate)<=$(=vEndDate)"}, Month=,Year=>}

[Amit_B]

Thanks for the reply.

I tried to simplify the example and solve it myself, but I'm not succeeding. I need 2 measures:

The first will count the number of orders each day and calculate the daily average for each month.
The solution you suggested works, but I'm unclear why the aggregation doesn't.

The problem is with the second measure (I didn't mention at first). The second metric computes the average of all monthly averages calculated in the first metric.
For this case, I have to aggregate because otherwise, I get an incorrect result.
I'm unable to create an aggregate table in the script.

[Or · Accepted solution]

Avg(Aggr(Count({<Date={">=$(=vStartDate)<=$(=vEndDate)"}, Year={$(=Max(Year)),$(=Max(Year)-1)}, Year=, Quarter=, Month=>}OrderID),Date))

First, those two bits I erased don't actually do anything. Best to remove them to keep the code clean.

Second, when you aggregate with set analysis, you should always consider whether the set needs to occur in the external function, aggr(), and/or internal function - the answer is often more than one of the three. In this case, I believe you'd also need to use the set for the Aggr() or you won't get the full range of potential dates, but without access to proper test data it's hard to be sure.