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
Anonymous
Not applicable

How to calculate the sales difference based on some criteria with DAX query?

Hello, I want find the sales difference value based on seq column in the report with a DAX formula. Please find the attached screen shot. Thanks in advance..
1 ACCEPTED SOLUTION
Kristjan76
Responsive Resident
Responsive Resident

Hi there,

 

You can try this, but there are some assumptions here. Each Seq number has a unique starting and end dates, if it has two rows with the same starting date, it will take the lower number, same applies with the end date except it will take the max.

IF the case is that SalesDiff will always be a positiv number, i.e. Sales is always higher at the EndDate, you do not need to calculate the fd (first date) and ld. You can just compute startSales and endSales without the FILTER function.

This would be a calculated column:

SalesDiff =
VAR seq = Table[Seq]
VAR tbl = 
  FILTER(
    Table,
    Table[Seq] = seq
  )
VAR fd = 
  MINX(
    tbl,
    Table[BeginDate]
  )
VAR ld = 
  MAXX(
    tbl,
    Table[EndDate]
  )
VAR startSales = 
  MINX(
    FILTER( tbl, Table[BeginDate] = fd),
    Table[Sales]
  )
VAR endSales = 
  MAXX(
    FILTER( tbl, Table[EndDate] = ld),
    Table[Sales]
  )
RETURN
endSales - startSales

If this solves your problem please give a LIKE and mark as solved.

Regards,

Kristjan76

View solution in original post

10 REPLIES 10
Kristjan76
Responsive Resident
Responsive Resident

Hi there,

 

You can try this, but there are some assumptions here. Each Seq number has a unique starting and end dates, if it has two rows with the same starting date, it will take the lower number, same applies with the end date except it will take the max.

IF the case is that SalesDiff will always be a positiv number, i.e. Sales is always higher at the EndDate, you do not need to calculate the fd (first date) and ld. You can just compute startSales and endSales without the FILTER function.

This would be a calculated column:

SalesDiff =
VAR seq = Table[Seq]
VAR tbl = 
  FILTER(
    Table,
    Table[Seq] = seq
  )
VAR fd = 
  MINX(
    tbl,
    Table[BeginDate]
  )
VAR ld = 
  MAXX(
    tbl,
    Table[EndDate]
  )
VAR startSales = 
  MINX(
    FILTER( tbl, Table[BeginDate] = fd),
    Table[Sales]
  )
VAR endSales = 
  MAXX(
    FILTER( tbl, Table[EndDate] = ld),
    Table[Sales]
  )
RETURN
endSales - startSales

If this solves your problem please give a LIKE and mark as solved.

Regards,

Kristjan76

Anonymous
Not applicable

Hi Kris,

 

Your solution works exactly as i need. Great Thank you very much.

However when i applied this formula on my relational data the second MAX(value) is returning wrong, LD date is not matching because of which i am not getting correct sales value within the "Seq" group.

 

So i have created two columns

 

 "Firstsales" : as column 1 based on your solution

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

 

Firstsales=
VAR seq = 'Table'[Seq]
VAR tbl =
  FILTER(
    'Table',
    'Table'[Seq] = seq
  )
VAR fd =
  MINX(
    tbl,
    'Table'[BeginDate]
  )
VAR startSales =
  MINX(
    FILTER( tbl, 'Table'[BeginDate] = fd),
    'Table'[Sales]
  )
RETURN
startSales

 

"Lastsale"  as 2nd column formula as

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

 

Lastsale=CALCULATE(MAX('Table'[Sales]),FILTER('Table','Table'[Seq] =EARLIER('Table'[Seq])))

 

and Then created a 3rd SalesDiff column by ( Lastsale - Firstsale) to get the results as indicated by you. I know this sounds very strange but this is working.

 

Thanks again for your great help..!

 

 

 

 

Greg_Deckler
Super User
Super User

I do not see any attached screen shot. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Unable to attach a screen shot intially, sorry for that. Please check the screen shot attached.

Thanks in advance.

 

Capture1.PNG

Hi,

 

Will there always only be 2 rows per Seq?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi,

Thanks for the response, No seq varies based on the begin date and start date for that perticular fisical year/ calender year.

Thanks

Hi,

 

So the logic should be to subtract the value against the fartherst end date for a certain Seq and the value against the current row.  Am i correct?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Yes, we can substract the value of the max date - min date within the sequence number group.

Hi,

 

Please take an example where you have more than two seq rows and show the expected result there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ahh, you are going to want to use EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.