how to calculate the average in one month when some data start or finish in the previous/next month

[dep]

As you can see I change one key parameter in my process in different date and then I keep it for several days. Then I change for some reason. I would like the monthly average, knowing that sometime I start in the previous month ......

Can someone help me to build the proper way to calculate?

Thanks

[bhanu_gautam · Accepted solution]

@dep Create a Date Table:
DateTable =
CALENDAR (DATE(2024, 1, 1), DATE(2025, 12, 31))

Add Year and Month Columns to Date Table:

DateTable =
ADDCOLUMNS (
DateTable,
"Year", YEAR([Date]),
"Month", MONTH([Date])
)

Calculate Daily Values: Assuming you have a table named Data with columns StartDate, EndDate, and Value:
DailyValues =
ADDCOLUMNS (
GENERATE (
Data,
CALENDAR (Data[StartDate], Data[EndDate])
),
"DailyValue", Data[Value] / (DATEDIFF (Data[StartDate], Data[EndDate], DAY) + 1)
)

Distribute Values Across Days:
DistributedValues =
SUMMARIZE (
DailyValues,
[Date],
"TotalDailyValue", SUM (DailyValues[DailyValue])
)

Aggregate Monthly Values:
DAX
MonthlyValues =
SUMMARIZE (
DistributedValues,
YEAR([Date]), MONTH([Date]),
"TotalMonthlyValue", SUM (DistributedValues[TotalDailyValue])
)

Calculate Monthly Average:
DAX
MonthlyAverage =
ADDCOLUMNS (
MonthlyValues,
"DaysInMonth", DAY (EOMONTH (DATE (MonthlyValues[Year], MonthlyValues[Month], 1), 0)),
"MonthlyAverage", [TotalMonthlyValue] / [DaysInMonth]
)