topic Re: Sum of all month except one month in DAX Commands and Tips

Sum of all month except one month

PBIBeginner2022 — Wed, 13 Jul 2022 09:01:12 GMT

Hi everyone,

I have a measure like this : CALCULATE(SUM(TPODeliveries[Delivered value in €]), TPODeliveries[Type] = "PO_Delivery")

This measure calculate the sum of Delivered value in € for each month of the year. However I need to calculate this sum for all month except one month. I have a column which contain the month that I don't want sum in my formula. Do you know the DAX function to add to my formula in order to sum all month without the month that I don't want which in my other column ?

Thanks in advance,

Re: Sum of all month except one month

amitchandak — Wed, 13 Jul 2022 11:24:05 GMT

PBIBeginner2022 , Based on what I got

a measure like

CALCULATE(SUM(TPODeliveries[Delivered value in €]), TPODeliveries[Type] = "PO_Delivery" ,TPODeliveries[Month] <> "Jan-2020" )

Re: Sum of all month except one month

johnt75 — Wed, 13 Jul 2022 11:28:39 GMT

CALCULATE(SUM(TPODeliveries[Delivered value in €]), TPODeliveries[Type] = "PO_Delivery" , NOT TPODeliveries[Month] IN VALUES('Table'[Unwanted month]) )

Re: Sum of all month except one month

PBIBeginner2022 — Wed, 13 Jul 2022 12:01:18 GMT

Thanks johnt75 ,

I try your solution but the result is not that I want.

I want a measure like this photo :

Re: Sum of all month except one month

PBIBeginner2022 — Wed, 13 Jul 2022 12:04:14 GMT

amitchandak ,

I search a mesaure where i don't need to write the month that I don't need beacause next month the month will be different. I want never sum the month just before the last monh:

Re: Sum of all month except one month

amitchandak — Wed, 13 Jul 2022 12:10:07 GMT

PBIBeginner2022 , if you have date,better to get using month year. You do not want jun when you are in August.

CALCULATE(SUM(TPODeliveries[Delivered value in €]), TPODeliveries[Type] = "PO_Delivery" , eomonth(TPODeliveries[Month],0) <> eomonth(today(),-2) )

CALCULATE(SUM(TPODeliveries[Delivered value in €]), filter(TPODeliveries, TPODeliveries[Type] = "PO_Delivery" && eomonth(TPODeliveries[Month],0) <> eomonth(today(),-2) ) )

In case you want use month name use format on top of eomonth(today(),-2)

format(eomonth(today(),-2), "mmm-yyyy")