topic Re: running total from a given date in DAX Commands and Tips

running total from a given date

ValeriaBreve — Thu, 01 Jun 2023 08:22:50 GMT

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)),

RETURN

Result

Re: running total from a given date

some_bih — Thu, 01 Jun 2023 10:37:12 GMT

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

Re: running total from a given date

ValeriaBreve — Thu, 01 Jun 2023 11:56:58 GMT

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

Re: running total from a given date

some_bih — Thu, 01 Jun 2023 13:25:01 GMT

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])

Adjust Sheet10 to your table name

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

Re: running total from a given date

ValeriaBreve — Thu, 01 Jun 2023 14:31:30 GMT

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)