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

 

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

     

     

4 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    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

      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'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