topic Re: SumIFs Statements in PowerBI in DAX Commands and Tips

SumIFs Statements in PowerBI

Anonymous — Thu, 09 Mar 2023 20:45:20 GMT

Hello,

I am currently trying to create a DAX formula similar to what I use in excel, unfortunately I am struggling with how to create it in PowerBI as the formulas I am using is not giving me the correct information.

I need to find the booking pace. In excel I use: = SUMIFS(Units ,Event Dates >= start of the month (January) , Event Dates <= End of the month (January) ,Bookings <= Today -1.

Any assistance would be greatly appreaciated.

Re: SumIFs Statements in PowerBI

andhiii079845 — Thu, 09 Mar 2023 21:07:09 GMT

=sumx(FILTER(table,table[Event Dates] >= "Startofmonth" && table[Event Dates] <= "Endofmonth" && table[Bookings]<=TODAY()-1 ),table[Units])

I hope I forget nothing. For better help show us a good data example and all involved tables.

Re: SumIFs Statements in PowerBI

FreemanZ — Thu, 09 Mar 2023 22:24:33 GMT

hi Anonymous

try like:

Measure =

SUMX(

FILTER(

TableName,

MONTH(TableName[Event Dates])=1

&&YEAR(TableName[Event Dates])=2023

&&TableName[Bookings]<TODAY()

TableName[Units]

)

Re: SumIFs Statements in PowerBI

Anonymous — Wed, 15 Mar 2023 20:39:50 GMT

I keep getting a syntax error with "<=" on the second statement where it states <= endofmonth

Re: SumIFs Statements in PowerBI

andhiii079845 — Wed, 15 Mar 2023 20:42:47 GMT

Please show your measure. 🙂

Re: SumIFs Statements in PowerBI

Anonymous — Thu, 13 Apr 2023 20:22:34 GMT

Hello,

Back again with the following Syntax: It is working now that I have removed the "<=" from Endofmonth because I was getting a syntax error. However, when I do the same formula in excel. I get a different answer than what PowerBi is displaying. Below is the DAX formula.

DCO Booking Pace = SUMX(

FILTER(

'Bookings Query',

'Bookings Query'[EventDate] > STARTOFMONTH('Bookings Query'[EventDate]) && ENDOFMONTH('Bookings Query'[EventDate]) && 'Bookings Query'[BookDate]<= TODAY()-1),

'Bookings Query'[Bookings])

Re: SumIFs Statements in PowerBI

FreemanZ — Thu, 13 Apr 2023 21:36:04 GMT

hi Anonymous

Are you calculating MTD? Measure or column? Without a calendar table, try a measure like:

DCO Booking Pace =

VAR currentmonth=

MAX('Bookings Query'[EventDate])

RETURN

SUMX(

FILTER(

'Bookings Query',

FORMAT('Bookings Query'[EventDate], "yymm"= FORMAT(currentmonth, "yymm"

&&Bookings Query'[EventDate]<TODAY()

'Bookings Query'[Bookings]

)