You need to create a DAX measure that will calculate the monthly revenue between the StartDate and EndDate. This measure will be filtered based on the context of the year and month columns from your calendar table.
Create a Date Filter Measure
IsDateValid =
VAR SelectedDate = MAX(Calendar[Date])
RETURN
IF(SELECTEDVALUE(Table[StartDate]) <= SelectedDate && SelectedValue(Table[EndDate]) >= SelectedDate, 1, 0)
Create a Monthly Revenue Measure
MonthlyRevenue =
CALCULATE(
SUM(Table[Revenue]),
FILTER(
Calendar,
Calendar[Date] >= SELECTEDVALUE(Table[StartDate]) &&
Calendar[Date] <= SELECTEDVALUE(Table[EndDate])
)
)
- Add a Matrix visual to your report.
- Drag the Year and Month columns from the Calendar table into the Rows section of the matrix.
- Drag your newly created MonthlyRevenue measure into the Values section.
- Ensure the StartDate and EndDate are used as filters on your report to show data between the valid dates.
To display the total at the bottom of the matrix, ensure the Totals option is enabled. You can manage totals in the format settings of the matrix.
The matrix will automatically calculate totals based on the visible months and years due to the context in the visual. No additional logic for the total is needed since Power BI will sum the visible rows.
- If you want to calculate a specific kind of total outside the regular summing process, you may need to modify the MonthlyRevenue measure to include additional filtering or aggregations based on your needs.
This approach will give you a dynamic table/matrix visual that calculates and sums up monthly revenues based on the given start and end dates. Let me know if you need further clarification!
