cancel
Showing results for
Did you mean:
Post Patron

## running total from a given date

Hello,

I am trying to compute a running total for a forecast per year, but not from the beginning of the year, just from the first date when there are no more actuals.

It is shown in a table per month/year.

I have come up with the formula below, however I cannot understand why my forecast is always summed from the beginning of the year (Jan 1st) - all dates are working correctly, LastDateWithSpending is giving me the right date, DATESBETWEEN is correctly starting from the LastDateWithSpending+1...

[Forecast] measure is just the sum of the Forecast column.

How do I force DAX to ignore the forecast from Jan 1st until the LastDateWithSpending and only start the cumulative sum from LastDateWithSpending+1 day?

WIth the formula below, I get correctly 0 for all months before the month of LastDateWithSpending, however the first non blank month is the sum of all forecast for the previous months since beginning of the year.

Thanks!

Kind regards

Valeria

Remaining Cumulative Forecast =
VAR LastDateWithSpending =
CALCULATE (
MAX ( 'Actuals'[Date] ),
ALL ( 'Actuals') ,
FILTER('Actuals',
'Actuals'[Spending USH\$]>0)
)
VAR LastVisibleDate = MAX(DateTable[Date])

VAR Result =
If(
LastVisibleDate>LastDateWithSpending,
CALCULATE(
[Forecast],
KEEPFILTERS(DateTable[Date]>LastDateWithSpending),
DATESBETWEEN(DateTable[Date],LastDateWithSpending+1,LastVisibleDate)),
0)

RETURN
Result

1 ACCEPTED SOLUTION
Memorable Member

Hi @ValeriaBreve  one of possible implementation is following:

1. In Date table insert column with code

IF([Date]>=EOMONTH(TODAY(),-2)
, TRUE,FALSE)
2. Measure for forecast is simple sum,
M_forecast = sum(Sheet10[Forecast])
3. Measure for Expected:
M_for_Expected =
CALCULATE([M_forecast],
'Date'[EOM and future]=TRUE
)
4. Finally, measure for YTD expected amount
M_Expected_test =
CALCULATE(
[M_forecast],
DATESYTD('Date'[Date]),
'Date'[EOM and future]=TRUE
)
End of Month below is column from Date column table
I hope this help

Sarajevo, Bosnia and Herzegovina
4 REPLIES 4
Memorable Member

Hi @ValeriaBreve  one of possible implementation is following:

1. In Date table insert column with code

IF([Date]>=EOMONTH(TODAY(),-2)
, TRUE,FALSE)
2. Measure for forecast is simple sum,
M_forecast = sum(Sheet10[Forecast])
3. Measure for Expected:
M_for_Expected =
CALCULATE([M_forecast],
'Date'[EOM and future]=TRUE
)
4. Finally, measure for YTD expected amount
M_Expected_test =
CALCULATE(
[M_forecast],
DATESYTD('Date'[Date]),
'Date'[EOM and future]=TRUE
)
End of Month below is column from Date column table
I hope this help

Sarajevo, Bosnia and Herzegovina
Post Patron

Thanks! This works 🙂

I still don't get why my code was not working... well I'll probably get to it when I am finished with the DAX course by Marco Russo and Alberto Ferrari! Give me a few months though - my brain is limited  😂

Thanks again :-0)

Post Patron

@some_bih Hello, it really looks quite simple...

But I can't figure it out.

I am adding a column with epected result, and another one with what I am getting today with my formula (so what I DON'T want).

And this should be by YEAR (so every following year the running total of the forecast is resetting and starting again from Jan)

Thanks!

Kind regards

Valeria

 Date Actuals Forecast Expected Result What I am getting today… Jan-23 56 67 Feb-23 26 30 Mar-23 35 47 Apr-23 26 29 May-23 35 35 208 Jun-23 37 72 245 Jul-23 63 135 308 Aug-23 30 165 338 Sep-23 26 191 364 Oct-23 39 230 403 Nov-23 35 265 438 Dec-23 55 320 493
Memorable Member

Hi @ValeriaBreve please provide some sample data and expected output with your solution.

Sarajevo, Bosnia and Herzegovina

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors