cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anikah Regular Visitor
Regular Visitor

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

Accepted Solutions
Kristjan76 Member
Member

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

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

10 REPLIES 10
Super User
Super User

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

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


Check-out my Back to School contest submission: Dinosaurs!

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

Proud to be a Datanaut!

anikah Regular Visitor
Regular Visitor

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

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

Thanks in advance.

 

Capture1.PNG

Super User
Super User

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

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...


Check-out my Back to School contest submission: Dinosaurs!

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

Proud to be a Datanaut!

Super User
Super User

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

Hi,

 

Will there always only be 2 rows per Seq?

anikah Regular Visitor
Regular Visitor

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

Hi,

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

Thanks

Super User
Super User

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

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?

Highlighted
anikah Regular Visitor
Regular Visitor

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

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

Super User
Super User

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

Hi,

 

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

Kristjan76 Member
Member

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

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,731 guests
Please welcome our newest community members: