Acumulate by month

[hectormanfredi]

Hello everyone, I really searched here a way to solve my problem and I tried many ways but none of them seemed to work out, so here we go...

This is the table I've got:

Date                                  Amount

---------------------------  ---------------------------

2018-06-28 00:00:00.000 10
2018-06-28 00:00:00.000 1
2018-06-28 00:00:00.000 5
2018-06-28 00:00:00.000 20
2018-06-27 00:00:00.000 -20
2018-06-27 00:00:00.000 -4
2018-06-26 00:00:00.000 1
2018-06-26 00:00:00.000 1

I need to ordenate accumulated amounts by month. I'd like to choose which month to start from (April, as in the following exemple):

Month   value       accumulated

April      3200        3200

May       1345        4545

June      4566        9111

July       1234        10345

OR

Month   value       accumulated

Sept      3200        3200

Oct       1345        4545

Nov      4566        9111

Dec       1234        10345

[drewlewis15]

First, make sure you have a calendar table to relate to your sales table.  Then, write the following DAX statement for accumulated value:

Accumulated Value =
CALCULATE (
    SUM ( Sales[Profit] ),
    FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

[hectormanfredi]

Hello @drewlewis15, thx for the reply...

Look what i've got:

And here the dax codeLook the numbers, they dont acumulate

And the sales table and calendar table are related

[drewlewis15]

Can you put the measure into a table so that I can see the totals month over month?  When I tested, it created the running total correctly.

You can also try the new Running Total Quick Measure.

[hectormanfredi]

 

Here @drewlewis15

[drewlewis15]

Does your data span across multiple years?  If so, make sure to add the year field to your visual.... That should fix the issue.

[hectormanfredi]

Yeah, have multiple years! But i only need 2018. I already tried this =( ... Do you have Skype? maybe talking there will be easier. If you have here's my: hector.manfredi@cna.com.br

Anyways, im sending here another print

I really dont have any cloue about what im doing it wrong 😞

[drewlewis15]

Not sure what you are doing wrong... Are you using your date field from your date table for the month?  When you drop that field, it should create a date hierarchy... Just Deselect quarter and day to leave month and year.  See screenshot below.

Also, add that same date field from your date table as a slicer.  That is how you will be able to dictate your starting point.  

[hectormanfredi]

That is exactly what im doing... Look:

[Vvelarde]

@hectormanfredi

Hi, please post a capture of your measure formula

Regards

Victor

[hectormanfredi]

Hello @Vvelarde here it is:

AccumulatedValue = CALCULATE(
SUM(DW_VendasComDevolucoes[QtdMd]);
FILTER(ALLSELECTED(DimCalendario[Date]);DimCalendario[Date] <= MAX(DimCalendario[Date]))
)

[hectormanfredi · Accepted solution]

Hello everyone, i appreciate you kindness and i'm very thankfull for your help.

I discover a difernt solution.

I edited the query and I referenced my sales table and in the date column I just pasted the date I wanted.

[hectormanfredi]

Hello everyone, i appreciate you kindness and i'm very thankfull for your help.

I discover a difernt solution.

I edited the query and I referenced my sales table and in the date column I just pasted the date I wanted.

[drewlewis15]