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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DH102
New Member

Difference column from a match lookup?

Hello,

I am not familiar enough with DAX to pull this off.

I have a table:

Date          Product     Item     % Usage of item

1/1/2020Product 1Water60
1/1/2020Product 1Salt40
1/1/2020Product 2Water30
1/1/2020Product 2Salt70
1/2/2020Product 1Water20
1/2/2020Product 1Salt80
1/2/2020Product 2Water45
1/2/2020Product 2Salt

55

 I am adding a column and I need to work out the diffrence between each item used from the previous Date (Water, Salt) . but filted out for each product on the second date entry (1/2/2020) like so:

Difference from previous run
-40
40
-15
-15

So getting the 20 from 1/2/2020, Product 1, Water and then deducting. 60 from 1/1/2020, Product 1 Water. to make -40 and so on

What Forumla would I use in DAX to pull this off when I add Custom Column in the Query Editor?

Appreciate your help in advance

 

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi  @DH102 

You need to knowledge that DAX is used to create a new Calculate Column/Table/Measure.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

1.JPG

M code is used in power query to create a custom column

2.JPG

 

https://community.powerbi.com/t5/Desktop/Dax-or-M-Language/td-p/136827

 

Then you could use this dax formula to create a new column

 

Difference from previous run = 
VAR previousdate=  CALCULATE(MAX('Table'[Date]),FILTER('Table', [Date]<EARLIER([date]) && [Product]=EARLIER([Product])&& [item]=EARLIER([item]))) return
'Table'[% Usage of item]-CALCULATE(SUM('Table'[% Usage of item]),FILTER('Table',[date]=previousdate && [Product]=EARLIER([Product])&& [item]=EARLIER([item])))

 

or

 

Difference from previous run 2 = 
VAR previousdate=  CALCULATE(MAX('Table'[Date]),FILTER('Table', [Date]<EARLIER([date]) && [Product]=EARLIER([Product])&& [item]=EARLIER([item]))) return
IF(ISBLANK(previousdate),BLANK(),'Table'[% Usage of item]-CALCULATE(SUM('Table'[% Usage of item]),FILTER('Table',[date]=previousdate && [Product]=EARLIER([Product])&& [item]=EARLIER([item]))))

 

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-lili6-msft
Community Support
Community Support

hi  @DH102 

You need to knowledge that DAX is used to create a new Calculate Column/Table/Measure.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns

1.JPG

M code is used in power query to create a custom column

2.JPG

 

https://community.powerbi.com/t5/Desktop/Dax-or-M-Language/td-p/136827

 

Then you could use this dax formula to create a new column

 

Difference from previous run = 
VAR previousdate=  CALCULATE(MAX('Table'[Date]),FILTER('Table', [Date]<EARLIER([date]) && [Product]=EARLIER([Product])&& [item]=EARLIER([item]))) return
'Table'[% Usage of item]-CALCULATE(SUM('Table'[% Usage of item]),FILTER('Table',[date]=previousdate && [Product]=EARLIER([Product])&& [item]=EARLIER([item])))

 

or

 

Difference from previous run 2 = 
VAR previousdate=  CALCULATE(MAX('Table'[Date]),FILTER('Table', [Date]<EARLIER([date]) && [Product]=EARLIER([Product])&& [item]=EARLIER([item]))) return
IF(ISBLANK(previousdate),BLANK(),'Table'[% Usage of item]-CALCULATE(SUM('Table'[% Usage of item]),FILTER('Table',[date]=previousdate && [Product]=EARLIER([Product])&& [item]=EARLIER([item]))))

 

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@DH102 , both as new columns

last Date = maxx(filter(table, [date]<earlier([date]) && [Product]=earlier([Product])&& [item]=earlier([item])),[date])
[% Usage of item] - maxx(filter(table, [date] =earlier([last Date]) && [Product]=earlier([Product])&& [item]=earlier([item])),[% Usage of item])

I must be making an elementary error

It gives me 

https://imgur.com/g3Y5Ki0

Tolken Literal

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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