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? 

 

Country/RegionDateDeathsCreated column
Brazil24-Apr-21   389,492    3,076
Brazil23-Apr-21   386,416    2,914
Brazil22-Apr-21   383,502    2,027
Brazil21-Apr-21   381,475    3,472
Brazil20-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

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/

     

     

7 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    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 

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello,

       

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

       

      thanks 

       

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

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