cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jotapece
Helper I
Helper I

Total previous value

Helo.

 

I have this data:

data

 

And I have this model:

conexion

 

I have this meassure:

aplusb

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

 

I have this other meassure:

aplusbprevious

 

This is the output:

output

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

 

Thanks in advance.

 

1 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

image.png

 

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

image.png

 

 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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

16 REPLIES 16
TomMartens
Super User
Super User

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

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany


@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!

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


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens, I having issues.... 

 

Captura

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

 

Any Ideas?

 

Thanks!

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

image.png

 

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

image.png

 

 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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

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
Helper I
Helper I

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

 

Greetings.

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

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

 

Thanks!

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

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

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
Helper I
Helper I

Here you can download the .pbix

 

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

 

Greetings.

jotapece
Helper I
Helper I

I leave the .pbix in this link.

 

Greetings.

jotapece
Helper I
Helper I

I leave the .pbix in this link.

 

Greentings.

jotapece
Helper I
Helper I

I leave the .pbix in this link.

 

Greetings.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!