Forum Discussion
Filter on the measure.
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
Community ChampionAnonymous
Try this...
Measure = CALCULATE(SUM(Sales[Sales])*SUM(Sales[QTY]), FILTER(CalendarTable, MAX(CalendarTable[Year])))
- Greg_Deckler
Anonymous, 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))
- Sean
Yes how you approach this depends - do you have a Calendar Table?
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))
- Sean
Anonymous change the filter part to look like this
FILTER(CalendarTable, CalendarTable[Year]=MAX(CalendarTable[Year])-1))
