topic Re: Visualise Amount over Months between dates in DAX Commands and Tips

Visualise Amount over Months between dates

JB_AT — Tue, 10 Sep 2024 11:38:42 GMT

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.

Re: Visualise Amount over Months between dates

123abc — Tue, 10 Sep 2024 12:49:01 GMT

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!

Re: Visualise Amount over Months between dates

Anonymous — Wed, 25 Sep 2024 06:50:27 GMT

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!