DAX Sumif Filtered Amount Between Date
Hi! I am a novice DAX user struggling to write the equivalent of SUMIF in Excel in DAX. I am currently working with DAX in the Excel Data Model environment. The dataset am I working with is contributions and distributions from/to multiple investors in multiple investments. The data table includes both contributions and distributions. The goal is to ultimately calculate the long-term/short-term nature of gain and loss per investor per investment on a first-in first-out basis. I am stuck on calculating the long-term contributions related to a particular(singular) distribution. Long-term contributions are those contributions the occured 366 days before the date of distribution. For example, the long-term contributions for Investor LP0001 in InvA related to the distribution on 2/9/2021 (row 4 below) equals $500,000 ($100,000 contribution on 5/14/209 and $400,000 contribution on 6/7/2019). I have created a long-term holding date calculated field, but I'm not sure that's even necessary. I'm open to all options. Thank you in advance! 🙂 Link to Sample Data
DAX Equivalent for Excel SUMIFS Where Lookup Field Contains Partial Text from Another Field
Morning all, I am relatively new to DAX and have scoured the forums but not found anything that quite works yet. I am trying to sum each record where the Member No is contained within the Hierarchy string e.g. the first Member No occurs in rows 1, 10 and 12 and sums to 23, whereas the 3rd Member No only appears in Hierarchy row 3 so sums to 3. The excel formula is: SUMIFS([Amount],[Hierarchy],"*"&[Member No]&"*") I tried adding "ab" to rows 5 & 9 as part of the attempt using the following formula to see if I could get close and then apply the "Like/contains" bit of logic but no luck: CALCULATE(SUM(Table1[Amount]),FILTER(Table1,FIND("ab",Table1[Hierarchy],,0)<>0)) I have also tried CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Member No]=EARLIER(Table1[Member No]))) as a start, also with the idea of getting close and then adding the Hierarchy element, also with no luck. Any help would be hugely appreciated! Hopefully the description is understandable. Thank you in advance.DAX Equivalent for Excel SUMIFS Where Lookup Field Contains Partial Text from Another Field
Morning all, I am relatively new to DAX and have scoured the forums but not found anything that quite works in Powerpivot/Dax yet. I am trying to sum each record where the Member No is contained within the Hierarchy string e.g. the first Member No occurs in rows 1, 10 and 12 and sums to 23, whereas the 3rd Member No only appears in Hierarchy row 3 so sums to 3. The excel formula is: SUMIFS([Amount],[Hierarchy],"*"&[Member No]&"*") I tried adding "ab" to rows 5 & 9 as part of the attempt using the following formula to see if I could get close and then apply the "Like/contains" bit of logic but no luck: CALCULATE(SUM(Table1[Amount]),FILTER(Table1,FIND("ab",Table1[Hierarchy],,0)<>0)) I have also tried CALCULATE(SUM(Table1[Amount]),FILTER(Table1,Table1[Member No]=EARLIER(Table1[Member No]))) as a start, also with the idea of getting close and then adding the Hierarchy element, also with no luck. Any help would be hugely appreciated! Hopefully the description is understandable. Thank you in advance.
