cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Subtract Values Row by Row with EARLIER function

Dear Contributors, 

 

Please help me to adjust the Calculated Column formula, so that the outcome was the same as the Measure.

Note, that for Calculated Column a Base value (Resources Needs) is not summarized by date, opposed to the Measure.

 

Difference Column = 'Resources Needs'[Resources Needs]-CALCULATE(MAX('Resources Needs'[Resources Needs]);FILTER('Resources Needs';'Resources Needs'[Project]=EARLIER('Resources Needs'[Project])&&'Resources Needs'[Position]=EARLIER('Resources Needs'[Position])&&'Resources Needs'[Recruitment Status]=EARLIER('Resources Needs'[Recruitment Status])&&'Resources Needs'[Date]<EARLIER('Resources Needs'[Date])))

 Capture Dif Column_Months Resources Needs Don't Summarized.JPG

 

 

Difference Measure = SUM('Resources Needs'[Resources Needs])-CALCULATE(SUM('Resources Needs'[Resources Needs]);PARALLELPERIOD('Resources Needs'[Date];-1;MONTH))

Capture Dif Measure.JPG

 

Pbix file is available here

 

Best regards,

Anna

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Frequent Visitor

Re: Subtract Values Row by Row with EARLIER function

I doubt that MIN, as well as MAX is correct for my case:

Capture Dif Column MAX and MIN.JPG

 

 

Basically I need (Sum [Resources Needs] within a Month)  - (Sum [Resources Needs] within a Previous Month), but in this case how to deal with the result:

  Capture Dif Column....JPG

View solution in original post

Super User IX
Super User IX

Re: Subtract Values Row by Row with EARLIER function

I guess where I was going with that is that you appear to have multiple matching rows so you either need to have better filters or you are going to need a different aggregation like AVERAGE or something.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Subtract Values Row by Row with EARLIER function

What happens if you substitute MIN for MAX in your column formula?


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Subtract Values Row by Row with EARLIER function

I doubt that MIN, as well as MAX is correct for my case:

Capture Dif Column MAX and MIN.JPG

 

 

Basically I need (Sum [Resources Needs] within a Month)  - (Sum [Resources Needs] within a Previous Month), but in this case how to deal with the result:

  Capture Dif Column....JPG

View solution in original post

Super User IX
Super User IX

Re: Subtract Values Row by Row with EARLIER function

I guess where I was going with that is that you appear to have multiple matching rows so you either need to have better filters or you are going to need a different aggregation like AVERAGE or something.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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
Top Kudoed Authors