calculate the accumulated

[gelsonwj]

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 · Accepted solution]

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

[gelsonwj]

You are my hero

[FreemanZ]

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]

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

[AlB]

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.

[gelsonwj]

Ok

[gelsonwj]

Chuva Acumulada_ =

  VAR __Table = GROUPBY('coperplan01 pluviometer_monitoring_list',[Date_short],"__Average",AVERAGEX(CURRENTGROUP(),[quantity]))

  VAR __Result = SUMX(__Table,[__Average])

RETURN

  __Result

[AlB]

@gelsonwj 

Try this measure that uses the one you have:

Chuva Acumulada_Cumul =
VAR currentDate_ = SELECTEDVALUE ( 'coperplan01 pluviometer_monitoring_list'[Date_short] )
VAR dates_ =
    FILTER (
        CALCULATETABLE (
            DISTINCT ( 'coperplan01 pluviometer_monitoring_list'[Date_short] ),
            ALLSELECTED ( 'coperplan01 pluviometer_monitoring_list'[Date_short] )
        ),
        [Date_short] <= currentDate_
    )
RETURN
    SUMX ( dates_, [Chuva Acumulada_] )

I suspect your [Chuva Acumulada_] measure can be simplified to a large extent, but I would need to see a sample of the  'coperplan01 pluviometer_monitoring_list' table and an example based on it to be sure

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.

[gelsonwj]

i would like to have a result like this 

this is the table. Each farm has its ID and a farm is composed of N rain gauges. In order to calculate the accumulated rain, it is necessary to make an average of each day, of all the rain gauges of a farm X, and day N is added to day N+1... "quantity" is the amount of rain in mm

[AlB]

@gelsonwj 

Does the suggestion in my previous post work?

If not I would need more details as to where it does not and some sample datga, ideally a pbix t work on

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.

[gelsonwj]

Did not work. Where can I send you the .pbix file?

[AlB]

You'd have to share here the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Or share the URL it via PM if you prefer

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]

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