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

Re: Total previous value

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

Highlighted
Super User III
Super User III

Re: Total previous value

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

Re: Total previous value


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

Highlighted
Super User III
Super User III

Re: Total previous value

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

Re: Total previous value

@TomMartens, I having issues.... 

 

Captura

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

 

Any Ideas?

 

Thanks!

Highlighted
Super User III
Super User III

Re: Total previous value

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

Highlighted
Helper I
Helper I

Re: Total previous value

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!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors