Apply FILTER to calculated YTD sum without removing the ALLEXCEPT groups

cerebro — Tue, 28 Sep 2021 12:00:50 GMT

hello everybody,

could please someone help me with the formula

the expression for revenue ytd works perfectly for my needs:

YTD_Act = CALCULATE ((TOTALYTD(SUM(Table[REVENUE]),Table[INVOICEDATE].[Date],"31.03")-TOTALMTD(SUM(Table[REVENUE]),Table[INVOICEDATE])),

ALLEXCEPT ( Table, Table[CUSTOMER_NAME]))

but i also had to upload some budget data to the same query and now i try to add a filter expression

Filter(Table,(Table(Source)<>"Budget")

and if i put this at the end of the whole line

YTD_Act = CALCULATE ((TOTALYTD(SUM(Table[REVENUE]),Table[INVOICEDATE].[Date],"31.03")-TOTALMTD(SUM(Table[REVENUE]),Table[INVOICEDATE])),ALLEXCEPT ( Table, Table[CUSTOMER_NAME]), Filter(Table,(Table(Source)<>"Budget"))

it doesn't recognise different customers for ytd revenue anymore and puts the same value (total revenue overall) in every line

Any ideas how to combine the expressions?

Thank you in advance

Re: Apply FILTER to calculated YTD sum without removing the ALLEXCEPT groups

VahidDM — Tue, 28 Sep 2021 12:13:35 GMT

Hi cerebro

It is because of that ALLEXCEPT ( Table, Table[CUSTOMER_NAME]) and Filter(Table,(Table(Source)<>"Budget")) in your code.

Can you share a sample of your data and result table here?

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

topic Re: Apply FILTER to calculated YTD sum without removing the ALLEXCEPT groups in DAX Commands and Tips

Apply FILTER to calculated YTD sum without removing the ALLEXCEPT groups

Re: Apply FILTER to calculated YTD sum without removing the ALLEXCEPT groups