Total previous value

[jotapece]

Helo.

I have this data:

And I have this model:

I have this meassure:

I used SUM and not SUMX beacause really I have two tables.

I have this other meassure:

This is the output:

How can I get total of the [Prevous A plus B]??

Thanks in advance.

[TomMartens]

Hey,

give this a try:

Previous A plus B =
SUMX(
'Table1'[Fecha]
,[Previous A plus B]
)

Hopefully this is what you are looking for

Regards

Tom

[jotapece]

---

@TomMartens wrote:

give this a try:

Previous A plus B =
SUMX(
'Table1'[Fecha]
,[Previous A plus B]
)

---

Hi @TomMartens

I think there's something wrong in your code. SUMX first parameter must be a table, isn't it?

Thanks!

[TomMartens]

Hey,

i forgot to encapsulate the column reference into a VALUES(), this turns the filtered values of the column into a 1-column table.

Regards
Tom

[jotapece]

@TomMartens, I having issues.... 

Now I get the total, but I don't understand what's the measure is doing.

Any Ideas?

Thanks!

[TomMartens · Accepted solution]

Hey,

here you will find a PBIX file. This file contains a really small amount of sample data 🙂

But besides that it's really close to your model.

I create these two measures:

A plus B = SUM(Table1[A]) + SUM(Table1[B]) 

and

PrevMonth A plus B = 
CALCULATE(
    [A plus B]
    ,PREVIOUSMONTH('Calendar'[Date])
)

Please be aware that I|m using the Calendar table in the measure to calculate the Previous Month Value.

As you can see from this screenshot both measure are recreating the primary issue, no sum for the total row

There is no value, because there is no active filter from the Calendar table.

For this reason, it's necessary to use SUMX() to iterate across the months.

The DAX statement for the measure I provided in my previous post was creating weird values due to the fact, the for each Day of the current month the previous month value was added.

For this it's necessary to iterate across the months and not the days.

Be aware that I'm using the column "Year-Month" from my Calendar table.

here is the measure:

SUMX PrevMonth A plus B = 
SUMX(
    VALUES('Calendar'[Year-Month])
    ,[PrevMonth A plus B]
)

And now it looks like this

 I would recommend that you change the relationship between your tables from 1:1 to 1 (your calendar table) to many (your fact table) and also adjust the filter direction from Both to Single, in most of the cases this is sufficient 🙂

Hopefully this is what you are lookinf for

Regards

Tom

[jotapece]

Thank you very much @TomMartens

I'm using CALENDARAUTO too and I have to move to CALENDAR in order to set the relationships in the way you told me.

It's works like charm!

[jotapece]

Any help? Sorry I'm insist, beacuse yesterday I had some problems to post.

Greetings.

[shebr]

Hi @jotapece

Silly question, but is the field set as a 'decimal' or whole number?

Are you able to share your pbix file?

Thanks

shebr

[jotapece]

Hi @shebr. I send you the .pbix via PM.

Thanks!

[shebr]

Hi @jotapece

Apologies for the delay. I checked your pbix and also did some of my own, and the same thing happens, I dont get a total value either. I will continue to work on it, and perhaps amend the dax for the measure for it to show a value. I will let you know if I get anywhere with it.

Thanks

shebr

[jotapece]

Sure. No problem.

I'm trying to figure it out in less elegant way, but I think should be a better easy way out.

Thank you for your time @shebr

[jotapece]

Hi @shebr

The fields A and B are whole number in this example, but in really there are decimal.

Here you can get pbix "https : // files.fm/u/3959kf74"

Remove the spaces. The forum delete my posts when I'm paste an url.

Thanks!

[jotapece]

Here you can download the .pbix

-----> https://files.fm/u/pspkfx2r

Greetings.

[jotapece]

I leave the .pbix in this link.

Greetings.

[jotapece]

I leave the .pbix in this link.

Greentings.

[jotapece]

I leave the .pbix in this link.

Greetings.