Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Calculate difference between values in different columns

Hi,

 

I would like to build the Excel visualization (see screenshot) in PBI. I assume this is possible with a DAX measure? I've been playing around with some DAX functions and tried some solutions from previous posts, but I cannot get my head around it.

 

It basically needs to calculate the difference in Amount compared to the Amount of the previous date. It should be dynamic; new dates can get added to the table.

 

Any hints, tips are appreciated.

 

Excel visualization.pngPBI visualization.png

 

Best regards,

Armand

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@az38 thank you for your proposal, but unfortunately it did not work out.

 

Eventually I found the solution using the following measures:

  1. PreviousDate
  2. Amount previous date
  3. Diff with prev date
  4. Diff %
PreviousDate = 
    VAR CurrentDate =
        SELECTEDVALUE ( 'Sheet1'[Date] )
    RETURN
        CALCULATE (
            MAX ( 'Sheet1'[Date] );
            ALLSELECTED ( Sheet1 );
            KEEPFILTERS ( Sheet1[Date] < CurrentDate )
        )
Amount previous date = 
VAR Prev = [PreviousDate]
RETURN
CALCULATE (
        [Total Amount];
        Sheet1[Date] = Prev
    )
Diff with previous = 
[Total Amount] - [Amount previous date]
Diff % = 
    IF (
        ISBLANK ( [Diff with previous] );
        BLANK ();
        DIVIDE ( [Diff with previous]; [Amount previous date] ) * 100
    )

View solution in original post

2 REPLIES 2
az38
Community Champion
Community Champion

Hi @Anonymous 

try a measure

diff = SELECTEDVALUE('Table'[Amount])-calculate(FIRSTNONBLANK('Table'[Amount];1);filter(ALLEXCEPT('Table';'Table'[Article]);'Table'[Date]<selectedvalue('Table'[Date])))

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

@az38 thank you for your proposal, but unfortunately it did not work out.

 

Eventually I found the solution using the following measures:

  1. PreviousDate
  2. Amount previous date
  3. Diff with prev date
  4. Diff %
PreviousDate = 
    VAR CurrentDate =
        SELECTEDVALUE ( 'Sheet1'[Date] )
    RETURN
        CALCULATE (
            MAX ( 'Sheet1'[Date] );
            ALLSELECTED ( Sheet1 );
            KEEPFILTERS ( Sheet1[Date] < CurrentDate )
        )
Amount previous date = 
VAR Prev = [PreviousDate]
RETURN
CALCULATE (
        [Total Amount];
        Sheet1[Date] = Prev
    )
Diff with previous = 
[Total Amount] - [Amount previous date]
Diff % = 
    IF (
        ISBLANK ( [Diff with previous] );
        BLANK ();
        DIVIDE ( [Diff with previous]; [Amount previous date] ) * 100
    )

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors