cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ValeriaBreve
Post Patron
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
some_bih
Memorable Member
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])
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
some_bih_0-1685625858865.png

 

 

Sarajevo, Bosnia and Herzegovina

View solution in original post

4 REPLIES 4
some_bih
Memorable Member
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])
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
some_bih_0-1685625858865.png

 

 

Sarajevo, Bosnia and Herzegovina

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)

ValeriaBreve
Post Patron
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

 

DateActualsForecastExpected ResultWhat I am getting today…
Jan-235667  
Feb-232630  
Mar-233547  
Apr-232629  
May-23 3535208
Jun-23 3772245
Jul-23 63135308
Aug-23 30165338
Sep-23 26191364
Oct-23 39230403
Nov-23 35265438
Dec-23 55320493
some_bih
Memorable Member
Memorable Member

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

Sarajevo, Bosnia and Herzegovina

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

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

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

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!

MPPC 2023 PBI Carousel

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