Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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!
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])
)
)
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.
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!
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |