Forum Discussion
Helper ICalculate with all function, maintaining filters applied
I am trying to calculate the sum of revenue for 2022 of a all products given that they have sales in a given periode (after 30.06.2022).
That is measure [Price 30.06] is not blank or 0.
The following is my attempt:
IF([Price 30.06] <> blank() || [Price 30.06]<>0, calculate([Revenue], Dates[Year]==2022, ALL(Products_table))
However, the all() formula seems to ignore the IF() command, and adds all products for the period.
Price 30.06 = calculated price after 30.06 // sum(DF_table(revenue)/sum(DF_table(quantity))
Revenue = sum(DF_table(revenue))
Products_table is connected to DF_table by item_id
Dates is connected to DF_table by dates column
Apologies, part of my inital syntax was incorrect, what solved it was the following:
CALCULATE(Revenue ,Dates[Year]==2022, KEEPFILTERS(Products[Products_ID] <>BLANK()), FILTER(ALL(Products), 'Price analysis'[Price 30.06] <> BLANK())),0)
2 Replies
- ribisht17
Super User
SalesAfter2Jan = CALCULATE(sum(SalesAfter[Sales]), FILTER(all(SalesAfter), SalesAfter[Date] >= DATE(2022,01,02)))(after and including 2 Jan)
Regards,
Ritesh
VeblengoodApologies, part of my inital syntax was incorrect, what solved it was the following:
CALCULATE(Revenue ,Dates[Year]==2022, KEEPFILTERS(Products[Products_ID] <>BLANK()), FILTER(ALL(Products), 'Price analysis'[Price 30.06] <> BLANK())),0)
