cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Minus Value between past date and new date with the same description

1.PNG

 

hi everyone ,i Unpivoted the columns and it turns like this

my purpose is : 

 

On 

1/26/2019 : Double fork is 7618 and On 1/27/2019 double fork is 7634 , these both will minus : Total = 7634-7618 

1/26/2019 : Double fork3 is 7346 and On 1/27/2019 double fork3 is 7357 , these both will minus : Total = 7357-7346 

 

How to minus the number together with the new date and past date with the same description i mentioned. i mean this will be repeatedly minus if the new date have , it will minus the old date. 

 

thx 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Minus Value between past date and new date with the same description

Hi @Chanleakna123 ,

 

You can try to use following calculation column formula to get value diff between same attribute different date :

Diff =
VAR prev =
    CALCULATE (
        MAX ( T2[Date] ),
        FILTER (
            ALL ( T2 ),
            [Attribute] = EARLIER ( T2[Attribute] )
                && [Date] < EARLIER ( T2[Date] )
        )
    )
RETURN
    IF (
        prev <> BLANK (),
        [Value] - LOOKUPVALUE ( T2[Value], T2[Attribute], [Attribute], T2[Date], prev )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Minus Value between past date and new date with the same description

Hi @Chanleakna123 ,

 

I'd like to suggest you to split attribute column to two part(description and number), it should more simply to look up similar descriptions for calculate.

 

If you are confused on coding formula, please share some sample data for test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Post Prodigy
Post Prodigy

Re: Minus Value between past date and new date with the same description

@v-shex-msft  i have shared link through drop box to you. can you please assist on this ? 

Thank you 

Highlighted
Post Prodigy
Post Prodigy

Re: Minus Value between past date and new date with the same description

hi @v-shex-msft  still awaiting your solution . can you please help me on this ? 

Thanks 

Highlighted
Community Support
Community Support

Re: Minus Value between past date and new date with the same description

Hi @Chanleakna123 ,

 

You can try to use following calculation column formula to get value diff between same attribute different date :

Diff =
VAR prev =
    CALCULATE (
        MAX ( T2[Date] ),
        FILTER (
            ALL ( T2 ),
            [Attribute] = EARLIER ( T2[Attribute] )
                && [Date] < EARLIER ( T2[Date] )
        )
    )
RETURN
    IF (
        prev <> BLANK (),
        [Value] - LOOKUPVALUE ( T2[Value], T2[Attribute], [Attribute], T2[Date], prev )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Highlighted
Post Prodigy
Post Prodigy

Re: Minus Value between past date and new date with the same description

@v-shex-msft  , thx you dear , it works so perfectly , you're brilliant. 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors