SUMIF >= column1 AND <= column2

[Akbarov]

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 )

[Akbarov]

Can anyone help me please?..

[Akbarov]

Any help please?

[amitchandak]

@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

[Akbarov]

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])

[amitchandak]

@Akbarov , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

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

[Akbarov]

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

[DimaMD · Accepted solution]

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

[Akbarov]

Thank you soooo much!

[DimaMD]

@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

[DimaMD]

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