Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create date difference value column ?

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?    ...
DATEDIFF
DAX
Previous row value
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

ValtteriN's avatar
ValtteriN
Community Champion
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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