cancel
Showing results for
Did you mean:
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
Highlighted
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

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

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.

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

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?

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

## 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?

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

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

Highlighted
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

Announcements

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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 48 members 1,362 guests
Recent signins: