Forum Discussion
Filter on the measure.
Community ChampionAnonymous
Try this...
Measure = CALCULATE(SUM(Sales[Sales])*SUM(Sales[QTY]), FILTER(CalendarTable, MAX(CalendarTable[Year])))
Community ChampionAnonymous, 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))
- 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))
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