Calculate net of VAT and un GBP with DAX
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,