cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lcopete
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
Ashish_Mathur
Super User
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/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
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/
lbendlin
Super User
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 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!