Forum Discussion

AaronToth's avatar
AaronToth
Icon for Resolver I rankResolver I
2 years ago
Solved

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

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


 

 

"DAX help"
DAX
  • Fowmy's avatar
    Fowmy
    2 years ago

    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's avatar
    Fowmy
    2 years ago

    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

7 Replies

  • 123abc's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      AaronToth
      Icon for Resolver I rankResolver I
      2 years ago
      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's avatar
        Fowmy
        Icon for Super User rankSuper User
        2 years ago

        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's avatar
    AaronToth
    Icon for Resolver I rankResolver I
    2 years ago

    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.


  • AaronToth's avatar
    AaronToth
    Icon for Resolver I rankResolver I
    2 years ago

    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.