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.
Hello,
I want to create a column like created column( current date - last date by country) in power bi.
I have country/Region, date and death column.
How do I create created column?
Country/Region | Date | Deaths | Created column |
Brazil | 24-Apr-21 | 389,492 | 3,076 |
Brazil | 23-Apr-21 | 386,416 | 2,914 |
Brazil | 22-Apr-21 | 383,502 | 2,027 |
Brazil | 21-Apr-21 | 381,475 | 3,472 |
Brazil | 20-Apr-21 | 378,003 | 0 |
Argentina | 24-Apr-21 | 432,312 | 90 |
Argentina | 23-Apr-21 | 432,222 | 679 |
Argentina | 22-Apr-21 | 431,543 | 309 |
Argentina | 21-Apr-21 | 431,234 | 1,000 |
Argentina | 20-Apr-21 | 430,234 | 0 |
thanks in advance
Solved! Go to Solution.
Hi @Anonymous
Try this code to add a new column :
Column =
VAR _CD = 'Table'[Date]
VAR _YD =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] < _CD
&& 'Table'[Country/Region] = EARLIER ( 'Table'[Country/Region] )
)
)
VAR _YDV =
CALCULATE (
MAX ( 'Table'[Deaths] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] = _YD
&& 'Table'[Country/Region] = EARLIER ( 'Table'[Country/Region] )
)
)
RETURN
IF ( ISBLANK ( _YD ), 0, 'Table'[Deaths] - _YDV )
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @Anonymous
Try this code to add a new column :
Column =
VAR _CD = 'Table'[Date]
VAR _YD =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] < _CD
&& 'Table'[Country/Region] = EARLIER ( 'Table'[Country/Region] )
)
)
VAR _YDV =
CALCULATE (
MAX ( 'Table'[Deaths] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] = _YD
&& 'Table'[Country/Region] = EARLIER ( 'Table'[Country/Region] )
)
)
RETURN
IF ( ISBLANK ( _YD ), 0, 'Table'[Deaths] - _YDV )
output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
If you want to do this in the query editor, have a look at my previous solution here:
https://community.powerbi.com/t5/Power-Query/Optimize-performance-at-Un-Cumulate-calculation/m-p/219...
You may want to review this simpler case explained with more detail first:
https://community.powerbi.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/m-p/2198969
Hello,
Your solution might not work as we have to take the country into the account as well.
thanks
The first one I linked to does handle this. The second one is the simpler example that does not.
Sure thanks, will take a look.
Hi,
Here is one way to this:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
Proud to be a Super User!
Hello,
Sorry, let me rephrase the problem.
I have to create a column with current date value - previous date value by country.
your solution is only giving the datediff.
thanks
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |