Calculate net of VAT and un GBP with DAX

[MattAnalytics]

Dear community,

I am looking for some help to write a DAX formula that would return the sales amount net of VAT and in GBP based on two columns. 

My current data model is composed of the following tables:

Actual { Property, Date, Currency, Food Sales, Beverage Sales}

VAT { Date, VAT, Item }                                              < Items = {Food Sales, Beverage Sales}

Currency  { Currency, Rate, Date }                             < Currency = { EUR/GBP }

Calendar { Date, Month ... etc }                                 < As Date Table

BusinessUnits { Property Name, Code, etc.... }

Note that the VAT and currency have different values over time.

I have the following relationships: 

Actual.Property 1 to many relationships with BusinessUnits.PropertyName

Actual.Date 1 to many relationships with Calendar.Date

VAT.Date 1 to many relationships with Calendar.Date

Currency.Date 1 to many relationships with Calendar.Date

This is my current formula that calculates my Net Food Sales. I'm not sure how to write the second part that would transform my EUR revenues into GBP. I'm wondering if I should use the VAR function, any recommendations?

Net Food Sales:=SUMX( Actual,

DIVIDE( Actual[Food Sales] ,

(1 + IFERROR(

LOOKUPVALUE(VAT[VAT],

VAT[Item] , "Food Sales", VAT[Date], Actual[Date]) , 0.2 ))

))

Also, would you recommend computing these calculations in PowerQuery or keep them in DAX?

Many thanks for your help.

Best,

[dedelman_clng]

Hi @MattAnalytics -

I can give you some generic pointers on DAX, but without a copy of your data/model/pbix it will be hard to give specific code. If you can share a copy of your pbix with sensitive data removed, we can get to specifics.

That being said, let me answer a couple of your questions:

---

I'm wondering if I should use the VAR function, any recommendations?

---

Assuming you mean using variables (VAR ... RETURN), it is a good practice, especially when dealing with lookups to use variables to do the heavy lifting and then at the end you return a simple expression. This also helps with troubleshooting as you can return the value that a variable calculates if the results are not what you expect. In case you're new to variables, syntax for using variables is

CalcName = 
VAR __MyVar1 = DAX expression...
VAR __MyVar2 = DAX expression...can use previous variables
RETURN
(usually simplified) DAX expression using variables from above

Also, would you recommend computing these calculations in PowerQuery or keep them in DAX?

---

Since it looks like this is a measure and not a calculated column, you would not be able to do this in PowerQuery, and DAX is the answer. Remember that a "column" in a table/matrix visual can be (often is) a measure, so don't confuse the two unless you are looking at a calculation that is specific for each row in the table.

Again, if you can share a copy of your PBIX (OneDrive, dropbox, google drive, github, etc), we can work on getting you a specific answer. If you can't, see this article for guidance: How to Get Your Question Answered Quickly 

Hope this helps

David