Forum Discussion

amirghaderi's avatar
amirghaderi
Icon for Helper IV rankHelper IV
5 years ago
Solved

Running Total combined from two tables

I have two running total measure from two different tables. they both have relationship with date table.  1. I want to have Forecast Curve to continute from max point of Actual Curve in the below gr...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    amirghaderi , Try to add both in forecast

     

    FCCum =
    VAR __Max2 = MAXX(ALL(t_P6_forecast),[Date])
    RETURN
    IF(MAX('Date'[Date]) >= __Max2,BLANK(),
    CALCULATE(
    sum(t_PE11[Actual MH]),
    FILTER(
    ALLSELECTED('Date'[Date]),
    ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
    ))+
    CALCULATE(
    sum(t_P6_forecast[Forecast Hour]),
    FILTER(
    ALLSELECTED('Date'[Date]),
    ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
    )
    ))

  • amitchandak's avatar
    amitchandak
    5 years ago

    amirghaderi , check what value you are getting for

    VAR __Max2 = MAXX(ALL(t_P6_forecast),[Date])

     

    That should stop

     

    or try allselected

    VAR __Max2 = MAXX(ALLSELECTED(t_P6_forecast),[Date])

     

    Any other date using that we can stop the forecast line 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

amirghaderi , Try to add both in forecast

 

FCCum =
VAR __Max2 = MAXX(ALL(t_P6_forecast),[Date])
RETURN
IF(MAX('Date'[Date]) >= __Max2,BLANK(),
CALCULATE(
sum(t_PE11[Actual MH]),
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
))+
CALCULATE(
sum(t_P6_forecast[Forecast Hour]),
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
)
))

amirghaderi's avatar
amirghaderi
Icon for Helper IV rankHelper IV
5 years ago

Thank you for your quick response.

It worked. But, how can I stop the line to extending after 2/3 month after reaching the maximum value as shown below:

 

 

 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    amirghaderi , check what value you are getting for

    VAR __Max2 = MAXX(ALL(t_P6_forecast),[Date])

     

    That should stop

     

    or try allselected

    VAR __Max2 = MAXX(ALLSELECTED(t_P6_forecast),[Date])

     

    Any other date using that we can stop the forecast line 

    • amirghaderi's avatar
      amirghaderi
      Icon for Helper IV rankHelper IV
      5 years ago

      VAR __Max2 = MAXX(ALLSELECTED(t_P6_forecast),[Date] worked since I had some other data as well in the table (not related to the selected filter).

       

      Thanks