topic Re: SUMIF >= column1 AND <= column2 in DAX Commands and Tips

SUMIF >= column1 AND <= column2

Akbarov — Mon, 22 Aug 2022 07:56:38 GMT

Hello,

I am trying to use following SUMIF formula for DAX measure:

=SUMIFS('(Table 1) Month'!G:G,
'(Table 1) Month'!A:A,Output!K17,
'(Table 1) Month'!B:B,Output!L17,
'(Table 1) Month'!C:C,">="&Output!M17,
'(Table 1) Month'!C:C,"<="&Output!N17)

DAX measure I am currently using is:

Period1 =

sumx(filter(allselected('(Table 1) Month'),

'(Table 1) Month'[Client] = [Client] &&

'(Table 1) Month'[Policy Year] = [Policy Year] &&

'(Table 1) Month'[Month] >=SELECTEDVALUE('Start Date'[Month From]) &&

'(Table 1) Month'[Month] <=SELECTEDVALUE('End Date'[Month To])),

'(Table 1) Month'[Days of Month])

But result is not correct...

Table1 and Table4 are related.

Table End and Start are offline tables ( not connected to any table, I use them just for filtering )

Re: SUMIF >= column1 AND <= column2

amitchandak — Mon, 22 Aug 2022 05:39:57 GMT

Akbarov ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can create a column first with multiple conditions, inside sumx measure

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Re: SUMIF >= column1 AND <= column2

Akbarov — Mon, 22 Aug 2022 06:03:27 GMT

Here you can see details. Results are not correct for some reason. Results are like x2 / x3

When filtering

Client to = A

Policy Year = 2020

Month from 202002 to 202006

Result have to be 151

But it gives result: 273

DAX measure I am currently using is:

Period1 =

sumx(filter(allselected('(Table 1) Month'),

'(Table 1) Month'[Client] = [Client] &&

'(Table 1) Month'[Policy Year] = [Policy Year] &&

'(Table 1) Month'[Month] >=SELECTEDVALUE('Start Date'[Month From]) &&

'(Table 1) Month'[Month] <=SELECTEDVALUE('End Date'[Month To])),

'(Table 1) Month'[Days of Month])

Re: SUMIF >= column1 AND <= column2

Akbarov — Mon, 22 Aug 2022 08:02:12 GMT

Any help please?

Re: SUMIF >= column1 AND <= column2

amitchandak — Mon, 22 Aug 2022 08:04:50 GMT

Akbarov , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: SUMIF >= column1 AND <= column2

Akbarov — Mon, 22 Aug 2022 08:13:26 GMT

Actuly my data is not really data so tutorial which you provided wont help me.

Can you please take a look?

https://drive.google.com/file/d/1dokknVDmqXdJSmb2yFrOwoRdIjVDhLfl/view?usp=sharing

Re: SUMIF >= column1 AND <= column2

Akbarov — Mon, 22 Aug 2022 10:10:02 GMT

Can anyone help me please?..

Re: SUMIF >= column1 AND <= column2

DimaMD — Mon, 22 Aug 2022 11:06:34 GMT

Akbarov Hi
Explain in more detail what you calculate? Your measure performs the correct calculation.
which is the value 151?

Re: SUMIF >= column1 AND <= column2

DimaMD — Mon, 22 Aug 2022 11:16:41 GMT

Akbarov Make two-way communication and your measure Period 2 will work,

Re: SUMIF >= column1 AND <= column2

Akbarov — Mon, 22 Aug 2022 11:30:08 GMT

Thank you soooo much!

Re: SUMIF >= column1 AND <= column2

DimaMD — Mon, 22 Aug 2022 14:10:25 GMT

Akbarov after having fun with Period1, the event is the following

Period1 = var _sel1 = SELECTEDVALUE('Start Date'[Month From]) var _sel2 = SELECTEDVALUE('End Date'[Month To]) var res = sumx( filter( '(Table 1) Month', AND('(Table 1) Month'[Client] = [Client], '(Table 1) Month'[Policy Year] = [Policy Year]) && AND( '(Table 1) Month'[Month] >= _sel1 , '(Table 1) Month'[Month] <= _sel2)), '(Table 1) Month'[Days of Month]) return res