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.

Anonymous try thisdelete the facts and add => comma and ( => , (

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

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))

Anonymous change the filter part to look like this FILTER(CalendarTable, CalendarTable[Year]=MAX(CalendarTable[Year])-1))

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

Filter on the measure. | Microsoft Fabric Community

Forum Discussion

29 Replies

Related content