Forum Discussion

ENGFAKAYODE's avatar
ENGFAKAYODE
Icon for Helper I rankHelper I
4 years ago
Solved

Running Total per month

Hi All,

I,ve been trying to get a running total from this table  I didnt get how the profit per month and I to get the running total per month restarting every year like this 

Any help would be appreciated

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi ENGFAKAYODE ,

    I have created a simple sample, but I cannot upload the pbix file. Please refer to my steps.

    First, insert the codes.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7JEYQwDETRXHyGxpKFllgo8k9jKJARc31lf/VxNO6km2zUlrYOc+ho53KzTWbeIT7ZJ+8i4D45Xg4Dv69185vVCOqVfpSGg0aVPXd0R7cqx8PGEK4w8c28G5yqnLxSXPOi2ulEnWGf2dO7DoRVPtkZnaueN/lvX6JgyGdeZhGf75TbRAN8jT5/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Profit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Profit", type number}})
in
    #"Changed Type"

    Then create a year column.

    year = YEAR('Table'[date])

    Finally create a measure.

    Measure =
CALCULATE (
    SUM ( 'Table'[Profit] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[year] = SELECTEDVALUE ( 'Table'[year] )
            && 'Table'[date] <= SELECTEDVALUE ( 'Table'[date] )
    )
)

    Or a column.

    Column =
CALCULATE (
    SUM ( 'Table'[Profit] ),
    FILTER (
        ( 'Table' ),
        'Table'[year] = EARLIER ( 'Table'[year] )
            && 'Table'[date] <= EARLIER ( 'Table'[date] )
    )
)

     

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • ENGFAKAYODE's avatar
    ENGFAKAYODE
    Icon for Helper I rankHelper I
    4 years ago

    Hi All,

    I,ve been trying to get a running total from this table  

    I didnt get how to calculate the profit per month and I want to get the running total per month restarting every year like this 

    Any help would be appreciated

    • nvprasad's avatar
      nvprasad
      Icon for Solution Sage rankSolution Sage
      4 years ago

      Hi ENGFAKAYODE,

      You need Date Column or Index Column for calculating running total.

      Appreciate a Kudos! ‌‌
      If this helps and resolves the issue, please mark it as a Solution! ‌‌

      Regards,
      N V Durga Prasad

      • ENGFAKAYODE's avatar
        ENGFAKAYODE
        Icon for Helper I rankHelper I
        4 years ago

        Ok,How can I create the date or index column.Am new to power bi

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
    Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
    Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi ENGFAKAYODE ,

    I have created a simple sample, but I cannot upload the pbix file. Please refer to my steps.

    First, insert the codes.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7JEYQwDETRXHyGxpKFllgo8k9jKJARc31lf/VxNO6km2zUlrYOc+ho53KzTWbeIT7ZJ+8i4D45Xg4Dv69185vVCOqVfpSGg0aVPXd0R7cqx8PGEK4w8c28G5yqnLxSXPOi2ulEnWGf2dO7DoRVPtkZnaueN/lvX6JgyGdeZhGf75TbRAN8jT5/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Profit = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Profit", type number}})
in
    #"Changed Type"

    Then create a year column.

    year = YEAR('Table'[date])

    Finally create a measure.

    Measure =
CALCULATE (
    SUM ( 'Table'[Profit] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[year] = SELECTEDVALUE ( 'Table'[year] )
            && 'Table'[date] <= SELECTEDVALUE ( 'Table'[date] )
    )
)

    Or a column.

    Column =
CALCULATE (
    SUM ( 'Table'[Profit] ),
    FILTER (
        ( 'Table' ),
        'Table'[year] = EARLIER ( 'Table'[year] )
            && 'Table'[date] <= EARLIER ( 'Table'[date] )
    )
)

     

    If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.