Forum Discussion

gelsonwj's avatar
gelsonwj
Icon for Helper I rankHelper I
3 years ago
Solved

calculate the accumulated

I created a measurement that averages the amount of rain in a day on a farm, now I would like to do the cumulative calculation, that is, the value of the next line is the sum of the previous lines

  • AlB's avatar
    AlB
    3 years ago

    gelsonwj 

    Your date_short column was of type text.  It needs to be of type date. That is why my first version was failing. It will work if you use this to create that column instead of the LEFT([date], 9) you had:

     

    Date_short = DATEVALUE([date])

     

    I have created another version that wil work with the months. Note that I have gotten rid of the date hierarchy (which I would strongly discourage as it can be the hidden cause of many problems)  and created a month column. It would be better to create a Year-Month column and better still to use a date table.

     

    teste26/12 V2 = 
VAR currentDate_ = MAX ( 'coperplan01 pluviometer_monitoring_list'[Date_short] )
VAR dates_ =
    FILTER (
        CALCULATETABLE (
            DISTINCT ( 'coperplan01 pluviometer_monitoring_list'[Date_short] ),
            ALLSELECTED (
                'coperplan01 pluviometer_monitoring_list'[Date_short],
                'coperplan01 pluviometer_monitoring_list'[Month]
            )
        ),
        [Date_short] <= currentDate_
    )
RETURN
    CALCULATE (
        SUMX ( dates_, [Chuva Acumulada_] ),
        ALLSELECTED (
            'coperplan01 pluviometer_monitoring_list'[Date_short],
            'coperplan01 pluviometer_monitoring_list'[Month]
        )
    )

     

    See it all at work in the attached file (Page 3)

     


    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

       

13 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    3 years ago

    gelsonwj 

    Your date_short column was of type text.  It needs to be of type date. That is why my first version was failing. It will work if you use this to create that column instead of the LEFT([date], 9) you had:

     

    Date_short = DATEVALUE([date])

     

    I have created another version that wil work with the months. Note that I have gotten rid of the date hierarchy (which I would strongly discourage as it can be the hidden cause of many problems)  and created a month column. It would be better to create a Year-Month column and better still to use a date table.

     

    teste26/12 V2 = 
VAR currentDate_ = MAX ( 'coperplan01 pluviometer_monitoring_list'[Date_short] )
VAR dates_ =
    FILTER (
        CALCULATETABLE (
            DISTINCT ( 'coperplan01 pluviometer_monitoring_list'[Date_short] ),
            ALLSELECTED (
                'coperplan01 pluviometer_monitoring_list'[Date_short],
                'coperplan01 pluviometer_monitoring_list'[Month]
            )
        ),
        [Date_short] <= currentDate_
    )
RETURN
    CALCULATE (
        SUMX ( dates_, [Chuva Acumulada_] ),
        ALLSELECTED (
            'coperplan01 pluviometer_monitoring_list'[Date_short],
            'coperplan01 pluviometer_monitoring_list'[Month]
        )
    )

     

    See it all at work in the attached file (Page 3)

     


    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

       

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi gelsonwj 

    Can you share the code of your current measure? It can probably be modified slightly to get to the cumulative.

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        3 years ago

        gelsonwj

        Can you paste the code in text, so that it can be copied, rather than on a screen cap?

         

         

        		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.

         

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi  gelsonwj 

     

    try to create a measure like this:

    YTD =
    VAR _date  = MAX(TableName[Date_short])
    RETURN
    CALCULATE(
        SUM(TableName[Chuva]),
        FILTER(
             ALL(TableName),
             TableName[Date_short]<=_date
        )
    )

     

    • gelsonwj's avatar
      gelsonwj
      Icon for Helper I rankHelper I
      3 years ago

      this doesn't work for me, because the SUM() function only accepts a table, and in my case I have a measure that calculates the accumulated rain