Create date difference value column ?

[Anonymous]

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

[VahidDM · Accepted 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/

[AlexisOlson]

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

[Anonymous]

Hello,

Your solution might not work as we have to take the country into the account as well. 

thanks 

[AlexisOlson]

The first one I linked to does handle this. The second one is the simpler example that does not.

[Anonymous]

Sure thanks, will take a look. 

[ValtteriN]

Hi,

Here is one way to this:

EarlierDatediff =

var _country = 'Table (5)'[Country/Region]

var _edate = CALCULATE(MAX('Table (5)'[Date]),FILTER('Table (5)','Table (5)'[Date] <EARLIER('Table (5)'[Date])),'Table (5)'[Country/Region]=_country)

var _datediff = DATEDIFF('Table (5)'[Date],_edate,DAY)

return

_datediff

End result (I modified 1 date to demo -2 in Argentina):

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!

[Anonymous]

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