Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
gritman
Frequent Visitor

Measure to Replicate a SUMIF based on Dates

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

 

 

 

 

   Variable34
      
RegionDate 1Date 2NumberAggregate (-3)Aggregate (-4)
Europe2-Jan5-Jan100100100
Europe3-Jan6-Jan200500600
Europe3-Jan6-Jan300500600
Europe4-Jan7-Jan40030003700
Asia4-Jan7-Jan50030003700
Asia4-Jan7-Jan60030003700
Asia4-Jan7-Jan70030003700
Asia4-Jan7-Jan80030003700
3 REPLIES 3
Anonymous
Not applicable

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.
ibarrau
Super User
Super User

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.

 


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

Happy to help!

LaDataWeb Blog

gritman
Frequent Visitor

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.