Running total by the end of last month

[Dunner2020]

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:

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?

[parry2k]

@Dunner2020 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.⚡

[parry2k]

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

[Dunner2020 · 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])

)

[parry2k]

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

[Dunner2020]

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

[parry2k]

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

[Dunner2020]

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

[parry2k]

@Dunner2020 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.⚡

[Dunner2020]

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

[aj1973]

Hi @Dunner2020 

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.