Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JB_AT
Helper III
Helper III

Visualise Amount over Months between dates

I have a table with the below data. StartDate has an active relationship with Calendar[Date] column

I want to visualise the Monthly Revenue in a matrix/table where the Month and Year columns of the Calendar table are being used where the startdate and enddate are valid. So from Feb 2024 to Feb 2025. I need the Total column to sum up based on whatever context is in the visual.



JB_AT_0-1725967925712.png

JB_AT_0-1725968316039.png

 

2 REPLIES 2
Anonymous
Not applicable

Hi @JB_AT ,

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your response!

123abc
Community Champion
Community Champion

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!

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.