Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowGet certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started
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 |
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.
Happy to help!
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
22 | |
17 | |
16 | |
11 | |
7 |
User | Count |
---|---|
26 | |
25 | |
12 | |
12 | |
12 |