cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
THU
Regular Visitor

Need help to define custom column to calculate the leftover amount per row

Hi, 

 I have the following issue. I want to create a custom column that calculates the left task of each day with the task i completed on that day and the total amout of tasks. Any idea how to do that?  

 

DateTask completedSum of TasksTasks left
23.07.202121715
24.07.202111714
25.07.202131711
26.07.20212179
27.07.20214175
28.07.20212173
29.07.20213170
1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

If you are trying to compute the Tasks Left column, see below code, and below explanation on how to get this code into Power Query to test with @THU 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc27DcAwCATQXagti59DPIvF/mvEEYlFQXHXPMGtBSwdrTMyQQPeIXtrgLeNmpAOauBIKAcp8KrezjBLpr99i3d1J2GzGkRwfwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Task completed" = _t, #"Sum of Tasks" = _t, #"Tasks left" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BS"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Task completed", Int64.Type}, {"Sum of Tasks", Int64.Type}, {"Tasks left", Int64.Type}}),
    #"Tasks Remaining" = 
        Table.AddColumn(
            #"Changed Type",
            "Tasks Remaining",
            each
                let
                    varDate = [Date]
                in
                [Sum of Tasks] - 
                List.Sum(
                    Table.SelectRows(
                        #"Changed Type",
                        each [Date] <= varDate
                    )[Task completed]
                ),
            Int64.Type
        )
in
    #"Tasks Remaining"

edhans_0-1627051205091.png

However, this will not perform well on larger data sets - anything over a few thousand records. Power Query isn't good at doing table scans. DAX is best for this.

edhans_1-1627051474514.png

Tasks Left to Complete = 
VAR varDate = MAX('Table'[Date])
VAR varTotalTasks = MAX('Table'[Sum of Tasks])
RETURN
    varTotalTasks -
    CALCULATE(
        SUM('Table'[Task completed]),
        'Table'[Date] <= varDate
    )

That measure will work over millions of records nearly instantly.
If that isn't what you need, please be more explicit in your request.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

If you are trying to compute the Tasks Left column, see below code, and below explanation on how to get this code into Power Query to test with @THU 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc27DcAwCATQXagti59DPIvF/mvEEYlFQXHXPMGtBSwdrTMyQQPeIXtrgLeNmpAOauBIKAcp8KrezjBLpr99i3d1J2GzGkRwfwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Task completed" = _t, #"Sum of Tasks" = _t, #"Tasks left" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BS"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Task completed", Int64.Type}, {"Sum of Tasks", Int64.Type}, {"Tasks left", Int64.Type}}),
    #"Tasks Remaining" = 
        Table.AddColumn(
            #"Changed Type",
            "Tasks Remaining",
            each
                let
                    varDate = [Date]
                in
                [Sum of Tasks] - 
                List.Sum(
                    Table.SelectRows(
                        #"Changed Type",
                        each [Date] <= varDate
                    )[Task completed]
                ),
            Int64.Type
        )
in
    #"Tasks Remaining"

edhans_0-1627051205091.png

However, this will not perform well on larger data sets - anything over a few thousand records. Power Query isn't good at doing table scans. DAX is best for this.

edhans_1-1627051474514.png

Tasks Left to Complete = 
VAR varDate = MAX('Table'[Date])
VAR varTotalTasks = MAX('Table'[Sum of Tasks])
RETURN
    varTotalTasks -
    CALCULATE(
        SUM('Table'[Task completed]),
        'Table'[Date] <= varDate
    )

That measure will work over millions of records nearly instantly.
If that isn't what you need, please be more explicit in your request.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

edhans
Super User III
Super User III

If you are trying to compute the Tasks Left column, see below code, and below explanation on how to get this code into Power Query to test with @THU 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc27DcAwCATQXagti59DPIvF/mvEEYlFQXHXPMGtBSwdrTMyQQPeIXtrgLeNmpAOauBIKAcp8KrezjBLpr99i3d1J2GzGkRwfwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Task completed" = _t, #"Sum of Tasks" = _t, #"Tasks left" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BS"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Task completed", Int64.Type}, {"Sum of Tasks", Int64.Type}, {"Tasks left", Int64.Type}}),
    #"Tasks Remaining" = 
        Table.AddColumn(
            #"Changed Type",
            "Tasks Remaining",
            each
                let
                    varDate = [Date]
                in
                [Sum of Tasks] - 
                List.Sum(
                    Table.SelectRows(
                        #"Changed Type",
                        each [Date] <= varDate
                    )[Task completed]
                ),
            Int64.Type
        )
in
    #"Tasks Remaining"

edhans_0-1627051205091.png

However, this will not perform well on larger data sets - anything over a few thousand records. Power Query isn't good at doing table scans. DAX is best for this.

edhans_1-1627051474514.png

Tasks Left to Complete = 
VAR varDate = MAX('Table'[Date])
VAR varTotalTasks = MAX('Table'[Sum of Tasks])
RETURN
    varTotalTasks -
    CALCULATE(
        SUM('Table'[Task completed]),
        'Table'[Date] <= varDate
    )

That measure will work over millions of records nearly instantly.
If that isn't what you need, please be more explicit in your request.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

THU
Regular Visitor

Thank you. Power Query works for now. I will have a look into DAX. Haven't used that before. 

edhans
Super User III
Super User III

Cool. Yeah, Power BI has two main components outside the visualization layer, DAX and Power Query. Power Query is best for shaping data and bringing it into the model, and DAX is best for analysis. Some things are black and white, but others are gray as to where it is best to do a particular task. Part of the learning journey!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors