Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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.
@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.
Ensure that your data is sorted by 'Country', 'Year', and 'Date' in ascending order. This is crucial for calculating the difference correctly.
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)
Replace 'YourTable' with the actual name of your table.
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))
This formula subtracts the previous day's count from the current day's count based on the index.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |