how to calculate between two columns from diffrent tables after sum in the visual

[davidi4524]

hi, i have  queri in power by that comes from sql.

the queri show all the orders ,year,month,and the total price for each line in the order.

i takes this queri to power bi and make a visual:

branch name and sum of total price. and put selector for year and for month.

the problem start when i whant to do a benchmark to the same branch in a difrent year or month.

so i took the same queri  in a diffrent name, and put it on the same dashbord, its work fine but:

if i whant to take the sum from year 2016 and calculate it - 2015?

example: the visual of brance name and sum of totalprice for 2016 is 150000

and the visual of brance name and sum of totalprice for 2015 is 148000

how its can be done???

please help me.

thanks

[dedelman_clng]

You should look into putting all order data in a single table (merge queries if need be) and you will need a date table in your model.  Then you can use time intelligence functions to calculate variance or any other measure.

Here is a good rundown of the time intelligence functions available: https://technet.microsoft.com/en-us/library/ee634763.aspx  You are probably wanting something like SAMEPERIODLASTYEAR

Hope this helps

David

[davidi4524]

I DONT THINK I KNOW HOW TO DO IT. is there a visual that ca do it?

its look like a simple and basic measure.. why its so complex??

there is an option to create one table? like vlookup in excel? 

[dedelman_clng]

If you haven't already, I would suggest going through the PowerBI guided learning that Microsoft offers:

PowerBI Guided Learning

This will discuss getting data (queries) and has examples on how to merge queries from different sources into one table.  It will then step you through was visualizations can and cannot do.  Towards the end there is some talk about DAX, the language of PowerBI and how it is used to create columns and measures.  I believe Time Intelligence is covered there as well.

Visualizations only put data into "image" form.  They can't really do the calculations for you (aside from basic ones like SUM or AVERAGE).  If you're not comfortable using DAX, you can always do the calculations in the data source and pull everything over as is, then put it in your visuals.

Hope this helps

David

[davidi4524]

ITS REALLY HELP ME IF YOU GIVE ME THE SYNTAX TO CALCULATE BETWEEN TWO OF THIS TABLES.

[dedelman_clng]

Here is pseudo-code.

ThisYearTotal = calculate(sum(order_amount))

LastYearTotal = calculate(sum(order_amount), SAMEPERIODLASTYEAR(order_date))

Variance = ThisYearTotal - LastYearTotal