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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
olavamd
Frequent Visitor

SUMIFS in BI / cumulative calculation pr. category with operator

Hi.

 

I want to calculate cumulative sales pr. period pr. project pr. forecast that shows how many units sold at a given time. Historic forecast is ofcourse actual.

In excel this was easy to do in column E, but i need help to find the right formula in Power BI.

Excel formula: =SUMIFS($D$2:$D$35;$C$2:$C$35;"<="&C35;$B$2:$B$35;B35;$A$2:$A$35;A35)

 

Appriciate the help !

 

ForecastProjectPeriodSalesforecastAccumulated sold pr. project/Q in forecast
Q218Highend2018071010
Q218Highend201808414
Q218Highend201809822
Q218Highend201810426
Q218Highend201811228
Q218Highend201812331
Q218Highend201901435
Q218Highend201902540
Q318Highend20180722
Q318Highend20180813
Q318Highend20180936
Q318Highend201810612
Q318Highend201811416
Q318Highend201812319
Q318Highend2019011029
Q318Highend201902231
Q318Highend201903435
Q318Highend2019041045
Q318Highend201905247
Q318Highend201906451
Q218Lowend20180777
Q218Lowend201808815
Q218Lowend201809823
Q218Lowend201810326
Q218Lowend201811127
Q218Lowend201812633
Q218Lowend201901134
Q218Lowend201902842
Q318Lowend2018101010
Q318Lowend201811414
Q318Lowend201812216
Q318Lowend201901218
Q318Lowend201902624
Q318Lowend201903933
Q318Lowend201904942
Q318Lowend201905648
Q318Lowend201906654
1 ACCEPTED SOLUTION
petrovnikitamai
Resolver V
Resolver V

add new column

new column = sumx(filter('TableName';and(and(earlier([Period])>=[Period];earlier[forecast]=[forecast]);earlier([project])=[project]));[Salesforecast])

View solution in original post

1 REPLY 1
petrovnikitamai
Resolver V
Resolver V

add new column

new column = sumx(filter('TableName';and(and(earlier([Period])>=[Period];earlier[forecast]=[forecast]);earlier([project])=[project]));[Salesforecast])

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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