cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Prodigy
Post Prodigy

Running total by the end of last month

Hi there,

 

I have a measure that calculates the running total until the last month. My measure looks like as follow:

 

Running total  =
var CurrentDate = MAX('Table'[Start Date ])
Var lastmonth =EOMONTH(TODAY(),-1)
Return
sumx(
filter(ALL('Table'),'Table'[Start Date]<=CurrentDate && 'Table'[Start Date] <=lastmonth ),  [calculated-value])
 
When I displayed the calculated measure on the graph it looks like as follow:
 

Screenshot 2021-01-18 105600.png

 

I was expecting the measure to plot the graph until December. However, it plotted the graph of the current month i.e. January. Could anyone guide me where I made the mistake?

 
1 ACCEPTED SOLUTION

Here is my updated measure:

Running Total = 

var CurrentDate = MAX('Table'[Start Date])
Var lastmonth =EOMONTH(TODAY(),-1)

Return
IF(CurrentDate <=lastmonth,
sumx(
filter(ALL('Table'),'Table'[Start Date)]<=CurrentDate),
[calculated-value])
)

View solution in original post

10 REPLIES 10
Super User III
Super User III

@leo_89 that's your problem, you are mixing dates, showing the x-axis from the date table, and doing the measure on fact table date column. change the date in your measure from your date table and it should work.

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Super User III
Super User III

@leo_89 also can you share the updated measure you are currently using?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Here is my updated measure:

Running Total = 

var CurrentDate = MAX('Table'[Start Date])
Var lastmonth =EOMONTH(TODAY(),-1)

Return
IF(CurrentDate <=lastmonth,
sumx(
filter(ALL('Table'),'Table'[Start Date)]<=CurrentDate),
[calculated-value])
)

View solution in original post

Super User III
Super User III

@leo_89 do you have a separate date table in your model? I'm a bit lost. 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Yes. I have separate date table in my model which is connected to the fact table. 

Super User III
Super User III

@leo_89 weird, do you have data for December 2020? Also what you are using on x-axis?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k , Yes I got the data for December. The last date for December is 28-12-2020. the x-axis contains the Month name which comes from the Date Table.

Super User III
Super User III

@leo_89 this is what you need to do in your measure, in fact, you are filtering your table in sumx but what that is doing just not adding the value of the current month in this case it is Jan 2021, but it is still returning the value for Jan 2021 up to Dec 2020, update your measure like this:

 

Running total  =
var CurrentDate = MAX('Table'[Start Date ])
Var lastmonth =EOMONTH(TODAY(),-1)
Return
IF (CurrentDate <= lastmonth,
sumx(
filter(ALL('Table'),'Table'[Start Date]<=CurrentDate  ),  [calculated-value])
)

 

Having said that, as a best practice you always want to add a date dimension in your model and perform time intelligence calculation based on this date dimension. There are many posts on how to add this dimension table, you can check my post on it there.

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k Thanks for the reply. I tried your recommended measure. Now the gaph shows data upto November month only as shown in the figure

leo_89_0-1610923432464.png

 

Memorable Member
Memorable Member

Hi @leo_89 

Well you called your measure Var lastmonth =EOMONTH(TODAY(),-1) to summit untill today. TODAY() in that function id the day of the current month. There is no mistake however if you don't want to see January of this year then select the graph and use the filter pane to fiter out the 2021 year.

 

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors