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 graph, rather than starting from zero. I also want to stop Forecast curve 3 month after it reaches its maximum rather than extending until the end of date table.

 

 

 

FCCum =
VAR __Max2 = MAXX(ALL(t_P6_forecast),[Date])
RETURN
IF(MAX('Date'[Date]) >= __Max2,BLANK(),
CALCULATE(
sum(t_P6_forecast[Forecast Hour]),
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
)
))
 
ActualMHCum =
VAR __Max = MAXX(ALL(t_PE11),[Expenditure Item Date])
RETURN
IF(MAX('Date'[Date]) > __Max,BLANK(),
CALCULATE(
    sum(t_PE11[Actual MH]),
    FILTER(
        ALLSELECTED('Date'[Date]),
        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
)
))
 
 
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 

5 Replies

  • 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 

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

    amirghaderi 

    Can you try these two measures?

    ActualMHCum =
VAR __Max =
    MAXX ( ALL ( t_PE11 ), [Expenditure Item Date] )
RETURN
    IF (
        MAX ( 'Date'[Date] ) > __Max,
        BLANK (),
        CALCULATE (
            SUM ( t_PE11[Actual MH] ),
            'Date'[Date] <= __Max,
             ALLSELECTED ('Date'[Date] )
        )
    )

    FCCum =
VAR __Max =
    MAXX ( ALL ( t_PE11 ), [Expenditure Item Date] )
VAR __Date =  MAX ('Date'[Date] )
RETURN
    IF (
        MAX ( 'Date'[Date] ) < __Max,
        BLANK (),
        CALCULATE (
            SUM ( t_P6_forecast[Forecast Hour] ),
            'Date'[Date] <= __Date,
            ALLSELECTED ('Date'[Date] )
        )
    ) + 
    CALCULATE (
        SUM ( t_PE11[Actual MH] ),
        'Date'[Date] <= __Max,
         ALLSELECTED ('Date'[Date] )
   )