topic Measure to Replicate a SUMIF based on Dates in DAX Commands and Tips

Measure to Replicate a SUMIF based on Dates

gritman — Tue, 20 Aug 2019 09:19:01 GMT

Hello there,

I'm trying to create a Power Bi measure to replicate a SUMIF in Excel.

Using the example data below, what I'd like to do, for each row, is sum up Number if Date 1 is <= Date 2 minus variable.

e.g. on the first rown, 5th Jan minus 3 days is 2nd Jan, so "Aggregate -3" is 100.

Then on the 2nd and 3rd rows, 6th Jan minus 4 days is 3rd Jan, so "Aggregate -3" is 500. Aggresate - 4 takes us back to the 2nd Jan, so that columns would have -600.

Any idea how I can do this in a Power Bi method please?

Many thanks

			Variable	3	4

Region	Date 1	Date 2	Number	Aggregate (-3)	Aggregate (-4)
Europe	2-Jan	5-Jan	100	100	100
Europe	3-Jan	6-Jan	200	500	600
Europe	3-Jan	6-Jan	300	500	600
Europe	4-Jan	7-Jan	400	3000	3700
Asia	4-Jan	7-Jan	500	3000	3700
Asia	4-Jan	7-Jan	600	3000	3700
Asia	4-Jan	7-Jan	700	3000	3700
Asia	4-Jan	7-Jan	800	3000	3700

Re: Measure to Replicate a SUMIF based on Dates

gritman — Tue, 20 Aug 2019 09:20:22 GMT

I should have added, the Variable is driven by a slicer, so the value of the measure needs to change as the slicer changes.

Many thanks

Re: Measure to Replicate a SUMIF based on Dates

ibarrau — Fri, 23 Aug 2019 17:50:16 GMT

Hi. I don't fully understand what is variable. But let's continue. I think you are looking something like this

VAR variable = ????
RETURN
SUMX (
    FILTER ( Table ,
    Table[Date1] <= Table[Date2] - variable
    ) ,
    Table[Number]
)

Hope this helps,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Measure to Replicate a SUMIF based on Dates

Anonymous — Sat, 24 Aug 2019 12:37:07 GMT

Do you still need an answer to this?

If you do, please tell us if the table you've given is a table as it's seen in Power Query or is it a table visual in one of your reports?

I think it's a table visual but want to confirm.

Best
D.