topic Re: Why my measure can't be filtered ? in DAX Commands and Tips

Why my measure can't be filtered ?

clementalba — Mon, 07 Aug 2023 12:06:54 GMT

Hi everyone,

I'm working on a table which contains informations about some orders and I want to display in a visual the sum of the current year sales and the sum of the previous year sales. Classic problem I guess, so I looked on internet and found a lot of topics for this but all were working with dates. However, In my tables the year is stored as an integer (ex: 2023, 2022...). So I built my own measure which works pretty well, I can obtain the annual sales for the year before the one selected in my slicer:

previous = VAR PY = SELECTEDVALUE(Clients[InvoiceYear]) - 1 RETURN CALCULATE ( [Sum of sales], FILTER ( ALL ( Orders ), Orders[InvoiceYear] = PY ) )

(I want to filter my data on the invoice year of the table Clients.)

But now I'm facing another problem : this measure can't be filtered. When I want to display it in a table with clients codes (stored in Clients table) for example, the value of the total year sales is displayed in each row and I'm not able to have the year sales by client and I really don't understand why.

Did someone can help me please ?

Best regards,

Clément

Re: Why my measure can't be filtered ?

eliasayyy — Mon, 07 Aug 2023 12:14:40 GMT

previous = VAR PY = MAX(Clients[InvoiceYear]) - 1 RETURN CALCULATE ( [Sum of sales], REMOVEFILTERS([column with slicer]), Orders[InvoiceYear] = PY )

Re: Why my measure can't be filtered ?

Greg_Deckler — Mon, 07 Aug 2023 13:42:43 GMT

clementalba Maybe:

previous = VAR PY = SELECTEDVALUE(Clients[InvoiceYear]) - 1 VAR ClientCode = MAX('Clients'[Code]) RETURN CALCULATE ( [Sum of sales], FILTER ( ALL ( Orders ), Orders[Client Code] = ClientCode, Orders[InvoiceYear] = PY ) )

Re: Why my measure can't be filtered ?

clementalba — Tue, 08 Aug 2023 06:30:13 GMT

Works great, thank you eliasayyy !