Forum Discussion

MaighreadL's avatar
MaighreadL
New Member
6 years ago
Solved

Calaculate the difference between one column if the other column contains a certain text

Hi There.  I was hoping you could help me with this.  I have a table where I would like the difference to be calculated based on the item number  So for example Item Number 22 I'd like the d...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI MaighreadL ,

     

     

    Create a Calculated Column

     

     

    Difference =
VAR a =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Item Number]
                = EARLIER ( 'Table'[Item Number] )
                && 'Table'[Date]
                    < EARLIER ( 'Table'[Date] )
        )
    )
VAR b =
    CALCULATE (
        MAX ( 'Table'[Unit Cost] ),
        FILTER (
            'Table',
            'Table'[Item Number]
                = EARLIER ( 'Table'[Item Number] )
                && 'Table'[Date] = a
        )
    )
RETURN
    IF (
        b
            <> BLANK (),
        'Table'[Unit Cost] - b
    )

     

     

     

     

    Regards,

    Harsh Nathani

    Appreciate with a Kudos!! (Click the Thumbs Up Button)

    Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI MaighreadL ,

 

 

Create a Calculated Column

 

 

Difference =
VAR a =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            'Table',
            'Table'[Item Number]
                = EARLIER ( 'Table'[Item Number] )
                && 'Table'[Date]
                    < EARLIER ( 'Table'[Date] )
        )
    )
VAR b =
    CALCULATE (
        MAX ( 'Table'[Unit Cost] ),
        FILTER (
            'Table',
            'Table'[Item Number]
                = EARLIER ( 'Table'[Item Number] )
                && 'Table'[Date] = a
        )
    )
RETURN
    IF (
        b
            <> BLANK (),
        'Table'[Unit Cost] - b
    )

 

 

 

 

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    MaighreadL I'm sure Anonymous solution will work, I didn't look at it but it can have performance implications on a large dataset. I would add a measure using the following DAX expression:

     

    Measure = 
VAR __tbl = ALL ( 'Table'[Id], 'Table'[DatePO] )
VAR __prevDate = CALCULATE ( MAX ( 'Table'[DatePO] ), FILTER ( __tbl, 'Table'[DatePO] < MAX ( 'Table'[DatePO] ) ) )
RETURN 
CALCULATE ( SUM ( 'Table'[Amount] ), __tbl, 'Table'[DatePO] = __prevDate)

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

  • MaighreadL's avatar
    MaighreadL
    New Member
    6 years ago

    Anonymous  This is perfect !!! Thank you so much !!!