Hi @lauriemclolo ,
The issue you are encountering is a classic DAX challenge rooted in how filter context operates within Power BI visuals. When your report visual uses the full period_begin_date in its columns, each column has a filter context for a single day, for example, period_begin_date = April 06, 2025. Your original DAX formula works here because the ALL(v_tcp_aggr_point[period_begin_date]) function successfully removes that single-day filter, allowing it to look at all previous dates to calculate the starting balance. However, when you switch the visual to use the Month-Year field, the context fundamentally changes. For a column like "Apr-2025", the entire data table is filtered to only include rows where the month is April. Your DAX measure attempts to remove the filter from the period_begin_date column, but it does not remove the persistent filter on the Month-Year column. Consequently, the calculation is trapped within the month of April and cannot "see" the data from March, February, or earlier, which is necessary to compute a starting balance. This inability to access prior months is why the measure returns blank values.
To solve this, the measure must be rewritten to first identify the time period it's in, then explicitly remove all surrounding filters to look back in time. The logic should be to determine the very first day of the month defined by the column's context (e.g., April 01, 2025) and then calculate the sum of all transactions that occurred before that day. It is also worth noting that a balance is typically calculated as inflows minus outflows. Your original formula adds all point values together. The formula below uses a more standard balance calculation of (Earned + Returned) - (Redeemed + Expired), which you can adjust if your business logic is different.
Starting Balance Monthly =
VAR LastDateInMonth =
MAX ( v_tcp_aggr_point[period_begin_date] )
VAR FirstDayOfCurrentMonth =
STARTOFMONTH ( LastDateInMonth )
RETURN
CALCULATE (
[Total Points Earded] + [Total Points Returned] - [Total Points Redeemed] - [Total Points Expired],
FILTER (
ALL ( v_tcp_aggr_point ),
v_tcp_aggr_point[period_begin_date] < FirstDayOfCurrentMonth
)
)
This corrected measure first finds the latest date within the current context (e.g., some day in April 2025) and uses STARTOFMONTH to establish the first day of that month as a boundary. The key to the solution is inside the CALCULATE function. By using FILTER(ALL(v_tcp_aggr_point), ...) it strips away all existing filters from the table, including the Month-Year = "Apr-2025" filter that caused the original problem. Once all data from all time periods is visible, it applies a new filter to sum only the transactions where the date is strictly less than the first day of the current month. This correctly computes the cumulative total up to the end of the previous month, providing the accurate starting balance for the month in the column.
As a final point of best practice, handling time intelligence calculations like this is made significantly easier and more efficient by creating a dedicated Date Table in your model. You would create a table with a continuous sequence of dates and columns for Year, Month, Month-Year, etc. After creating a relationship from this new table to your v_tcp_aggr_point table, you would use the columns from the Date Table in your visuals. This approach simplifies DAX and improves performance, as removing filters from a small, dedicated Date Table is more efficient than removing them from your entire, much larger data table.
Best regards,
