How to maintain accumulation at the turn of the year with DAX?

[Anonymous]

Greetings.
I am doing a month to month accumulation of direct costs but when I change the year the costs are reset.
Example of what I require:

• Accumulated to the month of December 2021: 150.000.
• Accumulated January 2022: 170.000

What actually happens:

• Accumulated to the month of December 2021: 150.000.
• Accumulated January 2022: 20.000

This is the function I am using: 

CumulativeCost = CALCULATE(
	SUM(Table[CostID]),
	FILTER(
        ALLSELECTED(Table[Month]),
		ISONORAFTER('Table[Month, MAX('Table[Month), DESC)
        )
)

I appreciate your help.

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and build a relationship (Many to One and Single) from the Date column appearing in the base data table to the Date column in the Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name column by the Month number.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures:

Cost = sum(Table[CostID])

Cumulative cost = calculate([Cost],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

[lbendlin]

That's a job for your calendar table. In that table the [Month] column has to be of the format yyyyMM (ie it has to include the parent as well, in this case the year).

However If you use the calendar table as your base table (dimension) and join it to your fact table via the date column then you can use all the built-in DAX time intelligence functions, including all the TOTAL*TD functions.  That is the preferred approach.