DAX Accumulated for each month

[ngomes]

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?

[v-sgandrathi]

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.

[bhanu_gautam]

@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])
)

[ngomes]

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

[lbendlin]

you need to radically simplify your data model

[ngomes]

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?

[v-sgandrathi]

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.

[ngomes]

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

    )

)

[v-sgandrathi]

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!

[ngomes · Accepted solution]

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
)

[v-sgandrathi]

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.

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[ngomes]

Good morning,

I am attaching the report with sample data
Report 

I am grateful for any help you can give me

[ngomes]

@lbendlin can you help?