cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User

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

Proud to be a Super User!

4 REPLIES 4
Super User

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

Proud to be a Super User!

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
Super User

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

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.