Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ENGFAKAYODE
Helper I
Helper I

Running Total per month

Hi All,

I,ve been trying to get a running total from this table  1st f.jpgI didnt get how the profit per month and I to get the running total per month restarting every year like this 2nd f.jpg

Any help would be appreciated

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

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] )
    )
)

 

vpollymsft_0-1660886953743.png

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.

 

View solution in original post

5 REPLIES 5
ENGFAKAYODE
Helper I
Helper I

Hi All,

I,ve been trying to get a running total from this table  1st f.jpg

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 2nd f.jpg

Any help would be appreciated

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

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

v-rongtiep-msft
Community Support
Community Support

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] )
    )
)

 

vpollymsft_0-1660886953743.png

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.

 

lbendlin
Super User
Super User

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-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors