topic Re: DAX Accumulated for each month in DAX Commands and Tips

DAX Accumulated for each month

ngomes — Thu, 10 Apr 2025 17:00:22 GMT

hello good afternoon,

I'm having a problem creating a DAX formula to calculate the accumulated value for each month up to the selected month

I have the formula with the following dax:

Approved sales n-1 =

CALCULATE(
[Sales],
YEAR(Sales[Date]) = SELECTEDVALUE('Calendar Approvals'[Year]), -- Current year for sales
'Approvals Calendar'[Year] = SELECTEDVALUE('Approvals Calendar'[Year])-1, -- Previous year for approvals
REMOVEFILTERS('Calendar Approvals'[Date].[Year]) -- Removes filters from year before
)

I'm trying to create a dax formula as follows:

Approved Sales n-1 Accumulated Month to Month =

VAR LastDateWithValues =
MAXX(
FILTER(
ALL('Sales'),
CALCULATE(
NOT ISBLANK([Approved Sales n-1])
)
),
'Sales'[Date]
)

VAR Value =
CALCULATE(
[Sales],
YEAR(Sales[Date]) = SELECTEDVALUE('Calendar Approvals'[Year]), -- Current year for sales
'Approvals Calendar'[Year] = SELECTEDVALUE('Approvals Calendar'[Year])-1, -- Previous year for approvals
REMOVEFILTERS('Calendar Approvals'[Date].[Year]) -- Removes filters from year before
)

RETURN

CALCULATE(
Value,
FILTER(
all(Sales[Date]), Sales[Date] <= LastDateWithValues
))

This formula only returns the value month by month, it does not accumulate.

Can someone help me?

Re: DAX Accumulated for each month

lbendlin — Thu, 10 Apr 2025 20:56:41 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX Accumulated for each month

ngomes — Fri, 11 Apr 2025 09:21:34 GMT

Good morning,

I am attaching the report with sample data
Report

I am grateful for any help you can give me

Re: DAX Accumulated for each month

ngomes — Fri, 11 Apr 2025 11:03:14 GMT

lbendlin can you help?

Re: DAX Accumulated for each month

bhanu_gautam — Fri, 11 Apr 2025 11:34:39 GMT

ngomes , Try using

dax
Approved Sales n-1 Accumulated Month to Month =
VAR CurrentYear = SELECTEDVALUE('Calendar Approvals'[Year])
VAR PreviousYear = CurrentYear - 1
VAR LastDateWithValues =
MAXX(
FILTER(
ALL('Sales'),
CALCULATE(
NOT ISBLANK([Approved Sales n-1])
)
),
'Sales'[Date]
)
VAR Value =
CALCULATE(
[Sales],
YEAR(Sales[Date]) = CurrentYear,
'Approvals Calendar'[Year] = PreviousYear,
REMOVEFILTERS('Calendar Approvals'[Date].[Year])
)
RETURN
CALCULATE(
Value,
FILTER(
ALL(Sales[Date]),
Sales[Date] <= LastDateWithValues
),
DATESYTD('Sales'[Date])
)

Re: DAX Accumulated for each month

ngomes — Fri, 11 Apr 2025 13:08:56 GMT

thanks for the reply but i still don't have accumulated values as you can see

Re: DAX Accumulated for each month

lbendlin — Fri, 11 Apr 2025 22:16:33 GMT

you need to radically simplify your data model

Re: DAX Accumulated for each month

v-sgandrathi — Mon, 14 Apr 2025 06:08:47 GMT

Hi ngomes,

Thankyou lbendlin for your reply on the issue.

I'm glad to hear that your query was resolved! If the response provided by the community member addressed your concern, kindly confirm.

Marking it as Accept Answer and give us Kudos if you found it helpful allows us to ensure that the solutions shared are valuable for the entire community.

Thank you.

Re: DAX Accumulated for each month

ngomes — Mon, 14 Apr 2025 10:13:21 GMT

Can you give more information?

I saw that you removed the calendar table and started using the approval date and the sale date in the DAX, but your DAX only validates whether the amount is collected or not with 1 and 0

How can I then calculate the accumulated value with this formula?

Re: DAX Accumulated for each month

v-sgandrathi — Mon, 14 Apr 2025 11:32:43 GMT

Hi ngomes,

You're right the DAX formula you referenced is only checking if data exists (returns 1 or 0), not actually summing sales.

To calculate accumulated approved sales month by month, you need to:
Shift the logic from "check if there's data" to "sum the actual sales values."
Make sure you're using a consistent and valid date column (ideally from a Date table).
Apply a filter that includes all dates up to the current row’s date, so Power BI can calculate the running total properly.

If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!

Thank you.

Re: DAX Accumulated for each month

ngomes — Mon, 14 Apr 2025 13:43:21 GMT

I validated all the variables and I can't find the error

- Selected year
- Year prior to the selected one
- Sum of this year's sales of projects approved last year
- the last date I had invoices

All these variables return the correct values, but I still can't calculate the accumulated value.

Measure used in power bi:

VAR CurrentYear = SELECTEDVALUE('Calendar'[Date].[Ano]) --Selected year

VAR PrevYear = SELECTEDVALUE('Calendar'[Date].[Ano]) - 1 --Year prior to the selected one

VAR Val = --Sum of this year's sales of projects approved last year

CALCULATE(

[Sales],

YEAR(Sales[Data]) = CurrentYear,

'Calendar'[Year] = PrevYear,

REMOVEFILTERS('Calendar')

)

VAR UltimaDataComValores = --the last date I had invoices

MAXX(

FILTER(

ALLSELECTED('sales'),

NOT(ISBLANK(val))

'sales'[Data]

)

RETURN

CALCULATE(

Val,

FILTER(

ALLSELECTED('sales'),

'sales'[Data] <= UltimaDataComValores

)

Re: DAX Accumulated for each month

v-sgandrathi — Tue, 15 Apr 2025 05:44:39 GMT

Hi ngomes,

To calculate an accumulated (running total) of current year sales for projects approved in the previous year, avoid storing the filtered sales in a variable like Val = CALCULATE(...). Variables return a static value and do not respond to row context, so they won’t accumulate correctly over time. Instead, apply the filtering and accumulation logic directly within the CALCULATE function using dynamic date context.

DAX Measure:

Approved Sales n-1 =

CALCULATE(

[Sales],

YEAR('Sales'[Data]) = SELECTEDVALUE('Calendar'[Date].[Ano]),

'Approvals Calendar'[Year] = SELECTEDVALUE('Calendar'[Date].[Ano]) - 1,

REMOVEFILTERS('Calendar')

)

Then your accumulated measure becomes very simple:

Approved Sales n-1 Accumulated =

CALCULATE(

[Approved Sales n-1],

FILTER(

ALL('Calendar'),

'Calendar'[Date] <= MAX('Calendar'[Date])

)

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Re: DAX Accumulated for each month

ngomes — Tue, 15 Apr 2025 15:22:09 GMT

I finally solved my problem, what I did was:
1 - I merged the two tables using the article code (ApprovedSales)
2 - I created a calendar table that I linked to the approval date field of the intercalated table
3 - I created a column where I validated which records had values to add, projects approved in the year prior to the selected one with sales in the selected year
4- I created a measure to have the accumulated sum

Thanks everyone for your help, see you next time 🙂

VAR Last =

CALCULATE(
max('ApprovedSales'[Sales.Date]),
ALL(Calendar), Approved Sales[Valid]=1)

RETURN

CALCULATE(
sum(ApprovedSales[Sales. Value]),
REMOVEFILTERS(ApprovedSales),
ApprovedSales[Sales.Date].[Date]<= Last,
ApprovedSales[Valid]=1
)

Re: DAX Accumulated for each month

v-sgandrathi — Wed, 16 Apr 2025 04:36:11 GMT

Hi ngomes,

I'm glad you found a solution and resloved the query. Thank you very much for sharing here.

Kindly mark your reply as the accepted solution so that others in the community can find it quickly.

Thankyou for connecting with Microsoft Community Forum.