How to calculate incremental values in Power BI using DAX query?

Question

I have a peculiar situation where I am trying to find the Incremental Values based on Cases/Deaths for country specific. Please see attached sample screenshot for reference on how my data looks.

[![Sample Data][1]][1]

I have 4 col - Day, Country , Cases/Deaths, Count. I have to calculate the increment of cases or deaths and then showcase the trend. My current file consists data at world level having all the countries with Days going for more than few months. I was able to perform the task in Excel with a small data as it was convenient to filter and then do the calculation.

And one more point, For Day 0, as there are no count of cases or deaths from previous day, we are considering Day 0 count to be incremental value for showcasing trends.

Can anyone guide on how to proceed with this task in Power BI? [1]: https://i.stack.imgur.com/4xNFV.png

user_3652345 · Answer

You can perform some Power Query transformation to achieve the required column. Here below is the code from Advanced Editor considering your table name Your_source_table_name

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc4xDoAgDEDRu3RmoLSgHsEzEAYiJLq44P0j6UCMdPrDW36MYMHAfpcr9x651dbrVoJkIqBixCjmFGNiMVLM4ybGigUbxL4vpebn7EU7rQyi6WTQMo38iBVyHlJ6AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [day = _t, country = _t, #"cases/death" = _t, count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"day", Int64.Type}, {"country", type text}, {"cases/death", type text}, {"count", Int64.Type}}),
    
    //-- New transformation starts from here
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"country", Order.Ascending}, {"cases/death", Order.Ascending}, {"day", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "day_prev", each [day] - 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"day", "day_prev", "country", "cases/death", "count"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns", {"country", "cases/death", "day_prev"}, #"Reordered Columns", {"country", "cases/death", "day"}, "Reordered Columns", JoinKind.LeftOuter),
    #"Expanded Reordered Columns" = Table.ExpandTableColumn(#"Merged Queries", "Reordered Columns", {"count"}, {"Reordered Columns.count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Reordered Columns",{{"Reordered Columns.count", "count_prev"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "increase", each if [count_prev] = null then [count] else [count] - [count_prev]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"increase", Int64.Type}})
in
    #"Changed Type1"

Here is the final output-

==========

You can also achieve the same using Measure with this below code-

increase_using_measure = 

VAR current_row_day = MIN(your_source_table_name[day])
VAR current_row_country = MIN(your_source_table_name[country])
VAR current_row_case_death = MIN(your_source_table_name[cases/death])
VAR current_row_count = MIN(your_source_table_name[count])

VAR previous_day_count = 
LOOKUPVALUE(
    your_source_table_name[count],
    your_source_table_name[day],current_row_day-1,
    your_source_table_name[country],current_row_country,
    your_source_table_name[cases/death],current_row_case_death
)

RETURN if(previous_day_count = BLANK(),current_row_count, current_row_count - previous_day_count)

Here is the output for both case-

How to calculate incremental values in Power BI using DAX query?

1 Answers