Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
3 years ago
Solved

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 mo...
Need Help
  • some_bih's avatar
    some_bih
    3 years ago

    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

     

     

ValeriaBreve's avatar
ValeriaBreve
Icon for Post Partisan rankPost Partisan
3 years ago

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