How to calculate the difference between a column value row by row

[AaronToth]

Hi,

I have a table that has the following columns : (Country, Year, Date, Count)
Here is a sample of the data

In the 'Count' column the value represents the total count.  If you look at rows :

Afganistan, 2020, March-10-20, 8
Afganistan, 2020, March-11-20, 11

This represents the total count at that time (up until that date).  This means on 11th 3 more cases came in (8 in total the previous day).  I need to find a way to calculate that difference.  I think if there was 1 country, this could be fairly easy, but the table includes all countries.  

How do I get this data into a table, or how to I augment this table to include that column?  I would assume there is an index needed and some sort of order (groupBy) that keeps the countries together.

Thanks,
Aaron

[Fowmy · Accepted solution]

@AaronToth 

If you are looking for the differernce in the count column by Date and Country, using this calculated column:

Diff = 
VAR __PreCount = 
    MAXX( 
        OFFSET( -1 , ALL(Table20[Country],Table20[Date],Table20[Count]) , PARTITIONBY( Table20[Country] ) ) ,
        Table20[Count] 
    )
VAR __Result =  IF( NOT ISBLANK( __PreCount ) ,  Table20[Count] -  __PreCount )
RETURN
    __Result

[Fowmy · Accepted solution]

@AaronToth 

If you are looking for the differernce in the count column by Date and Country, using this calculated column:

Diff = 
VAR __PreCount = 
    MAXX( 
        OFFSET( -1 , ALL(Table20[Country],Table20[Date],Table20[Count]) , PARTITIONBY( Table20[Country] ) ) ,
        Table20[Count] 
    )
VAR __Result =  IF( NOT ISBLANK( __PreCount ) ,  Table20[Count] -  __PreCount )
RETURN
    __Result

[AaronToth]

Thanks for this solution.  I am very close, and just trying to remove one syntax error.

PARTITIONBY( 'table'[Country] ) ) ,
is producing the following error
"The OrderBy parameter only accepts the ORDERBY Function"

[Fowmy · Accepted solution]

@AaronToth 

Re-check how you have applied may be you missed a comma or try the following:

Diff = 
VAR __PreCount = 
    MAXX( 
        OFFSET( -1 , 
            ALL(Table20[Country],Table20[Date],Table20[Count]),
            ORDERBY(  Table20[Date]),        
            PARTITIONBY( Table20[Country] ) ) ,
        Table20[Count] 
    )
VAR __Result =  IF( NOT ISBLANK( __PreCount ) ,  Table20[Count] -  __PreCount )
RETURN
    __Result

[AaronToth]

Thanks again.  I checked and it looks good now.  If you look at your two posts (I could be wrong) you will see the first does NOT include the OrderBy.  Regardless, excellent solution and thank you for that. 

[AaronToth]

@123abc - I tried SHIFT + SELECT on two columns in the table view and it appears I can't do it that way.  I have also tried using the "Sort Column" menu in the table view with no success.  

[AaronToth]

@123abc - How would one sort 3 columns in a table in ASC order?  It would make sense that the "Countries" are group together, with their respective dates in ASC order.  Why does the country order matter if they are all together?  

I will do it, but I'm having a hard time even understanding how to sort 2 or more columns in a standard table.

Thanks again for your help, I am really excited to try this after I just make sure of #1 first.

[123abc]

1. Ensure that your data is sorted by 'Country', 'Year', and 'Date' in ascending order. This is crucial for calculating the difference correctly.

2. Create an Index Column: If your data doesn't already have a unique index column, you can create one. In Power BI Desktop, go to the 'Modeling' tab, click on 'New Column', and use the following formula to create an index column:

Index = RANKX(ALL('YourTable'), 'YourTable'[Country],,ASC) + RANKX(ALL('YourTable'), 'YourTable'[Year],,ASC) + RANKX(ALL('YourTable'), 'YourTable'[Date],,ASC)

1. Replace 'YourTable' with the actual name of your table.

2. Create a Calculated Column for the Difference: Now, create a new calculated column using the following formula to calculate the difference:

CountDifference = 'YourTable'[Count] - CALCULATE(MAX('YourTable'[Count]), FILTER(ALL('YourTable'), 'YourTable'[Index] = 'YourTable'[Index] - 1))

1. This formula subtracts the previous day's count from the current day's count based on the index.

2. Remove the Index Column: You can hide or remove the index column if you don't want it to be visible in your report.

After following these steps, you should have a new column ('CountDifference') that represents the difference in the 'Count' column row by row. Please make sure to replace 'YourTable' with the actual name of your table.

Remember that this approach assumes your data is sorted correctly, and the 'Date' column is in a date format recognized by Power BI. If your 'Date' column is not in a date format, you may need to convert it using the 'Date' type in Power BI.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.