Advanced Accumulation

To accumulate a value with a start and end date and filters, you can use the following formula in DAX:

CALCULATE(        
	CALCULATE(
		SUM('table'[value]),
		FILTER(
			ALL(table),
			YEAR(table[date]) = YEAR(TODAY()) &&
			table[date] <= MAX(table[date]) &&
			table[filter] = "some_string"                
		)
	),
	FILTER(
    	table,
  		table[date] <= [current_date]
    )
)

This formula first calculates the sum of a specific column (in this case, "value") in a table (in this case, "table"). It then applies two filters to the table using the FILTER function. The first filter includes conditions for the current year (i.e., YEAR(table[date]) = YEAR(TODAY())), the date range up to the latest date in the table (i.e., table[date] <= MAX(table[date])), and any additional filters you may need (in this case, table[filter] = "some_string"). The second filter limits the calculation to a specific end date, which is represented by the [current_date] parameter.

Note that the inner CALCULATE function is used to calculate the sum of the "value" column for the filtered data set. This is necessary because the outer FILTER function limitsthe calculation to a specific end date, whereas the inner FILTER function limits the calculation to a specific date range and any additional filters.

Also note that the YEAR function is used to extract the year from the table[date] column, which is then compared to the current year obtained using the TODAY function. This ensures that only records from the current year are included in the calculation.

Finally, the formula uses the MAX function to obtain the latest date in the table, which serves as the upper bound for the date range filter. This ensures that the calculation includes all records up to the latest date in the table, regardless of the [current_date] parameter.

Overall, this formula is a powerful tool for aggregating data over a specific date range and applying additional filters as needed. By using the CALCULATE function and multiple FILTER functions, you can customize the calculation to suit your specific needs and ensure that it accurately reflects the data set you are working with.