cancel
Showing results for
Search instead for
Did you mean:
Regular Visitor

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

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.

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
2 REPLIES 2
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

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.

## Helpful resources

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors