Filter on the measure.

[vitexo87]

I need to create a measure that makes a calculation using the SUMX function but also has a filter, for example SUMX (table; max (year column); column1 * column2) and will also need to create a measure that do the same thing however using the previous year, for example SUMX (table, (max (year column) - '); * column1 column2), made several attempts however unsuccessfully.

[Greg_Deckler]

The general formula for a measure like that is:

SUMX(FILTER(...),...)

Or you can use CALCULATE

CALCULATE(SUM(...),FILTER(...))

without sample data, not sure how much more I can help.

[vitexo87]

@Greg_Deckler

I'm trying it

(SUMX (FILTER (DMEGF_Ano; max (DMEGF_Ano [Year])) facts, facts[Vl_PedidoCompra] * facts [Qtd_PedidoCompra]))

but it is giving error

[Sean]

@vitexo87 try this

delete the facts and add => comma and ( => , (

[vitexo87]

@Sean @Greg_Deckler

I did as suggested however without success, maybe I'm trying to make wrong way, what I need is to get the value of a calculation over a year filtered and also the value of it in anetrior period to the filtrate, for example, I want to know the total sales of the year 2016 and in a different measure present value of 2015

[Sean]

@vitexo87 

Try this...

Measure = CALCULATE(SUM(Sales[Sales])*SUM(Sales[QTY]), FILTER(CalendarTable, MAX(CalendarTable[Year])))

[Greg_Deckler]

@vitexo87, @Sean's formula assumes a relationship between the Sales table and Calendar table based upon a Date field.

To get the 2015, just add a "-1", like "MAX(CalendarTable[Year])-1"

Sticklers would tell you to do it in three measures:

PedidoCompra = facts[Vl_PedidoCompra] * facts [Qtd_PedidoCompra]

ThisYear = CALCULATE(PedidoCompra,FILTER (DMEGF_Ano; max (DMEGF_Ano [Year])))

LastYear = CALCULATE(PedidoCompra,FILTER (DMEGF_Ano; max (DMEGF_Ano [Year])-1))

[vitexo87]

@Greg_Deckler @Sean

I have two tables that use for pedro filter that are the tables of the year and month

this way the measure tells me the total amount changing it when it when the filter is changed but adding -1 he did not show the

value of the previous year,

Measure = CALCULATE(SUMX(Fatos; Fatos[Vl_PedidoCompra]*fatos[Qtd_PedidoCompra]); FILTER(DMEGF_Ano; MAX(DMEGF_Ano[Ano])-1))

[Greg_Deckler]

My bad, try:

Measure = CALCULATE(SUMX(Fatos; Fatos[Vl_PedidoCompra]*fatos[Qtd_PedidoCompra]); FILTER(DMEGF_Ano; DMEGF_Ano[Ano] = MAX(DMEGF_Ano[Ano])-1))

[vitexo87]

@Greg_Deckler @Sean

Now work out, is there any function I can subistituir the MAX function, because with the Max function I'll just have the value 2015 if I apply the 2015 filter the returned value will be blank

[Greg_Deckler]

@vitexo87 - I don't understand what you are asking, you can substitute just about anything in there, even = 2015 if you want to hard code it.

[Sean]

This is why it's good to have a calendar table? I thought you have one?

you can then do Year to Date and Previous Year - which will adjust with the slicers 

[vitexo87]

@Sean @Greg_Deckler

The dates of tables I have are two, one that tells the year and one that informs the month both are relacionan through the fact table, just need to now see a way to use this as VOES passed me without the MAX function in time determine the year to calculate

[Sean]

@vitexo87 These Measure give Year and Previous Year and Year on Year Change and Year On Year %

Add the Year Slicer and then select at least 2 consective years!

Max Year = CALCULATE([Total Net], FILTER(Payments, Payments[PMT Year]=MAX(Payments[PMT Year])))

Max Year -1 = CALCULATE([Total Net], FILTER(Payments, Payments[PMT Year]=MAX(Payments[PMT Year])-1))

YoY Change = [Max Year] - [Max Year -1]
YoY % Change = DiVIDE ( [YoY Change], [Max Year -1], 0)

Let me know if you do decide to add a Caledar Table - there's a better way to do this.

[vitexo87]

@Sean @Greg_Deckler

I tried the following expression and the value is blank:

Consumption Warehouse 2 = calculate (sum (Facts [Vl_ConsumoAlmox]) - sum (Facts [Vl_EstornoAlmox]), FILTER (DMEGF_Ano; DMEGF_Ano [Year] = MAX (DMEGF_Ano [Year]) - MAX (DMEGF_Ano [Year]) - 1))

[Greg_Deckler]

Consumption Warehouse 2 = calculate (sum (Facts [Vl_ConsumoAlmox]) - sum (Facts [Vl_EstornoAlmox]), FILTER (DMEGF_Ano; DMEGF_Ano [Year] = (MAX (DMEGF_Ano [Year]) - 1)))

[Sean]

@vitexo87

                                                                                                           2016  - ( 2016 -  1) => 1 

FILTER (DMEGF_Ano; DMEGF_Ano [Year] = MAX (DMEGF_Ano [Year]) - (MAX (DMEGF_Ano [Year]) - 1)))

delete the underlined part

[vitexo87]

@Sean @Greg_Deckler

correct the error and the information is being displayed however when I do a filter annually whether in 2016, 2015 or 2014 values are blank

[Sean]

are you using DMEFG_Ano[Year] for the Slicer?

[vitexo87]

@Sean

yes, to correct that what you recommend? I create a dimenssão calendar?

[Sean]

Do you have any other filters/slicers ON at the same time?

EDIT:

If you want to create a Date Table look here (Link by Matt)

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/