cancel
Showing results 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.

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 !

 Forecast Project Period Salesforecast Accumulated sold pr. project/Q in forecast Q218 Highend 201807 10 10 Q218 Highend 201808 4 14 Q218 Highend 201809 8 22 Q218 Highend 201810 4 26 Q218 Highend 201811 2 28 Q218 Highend 201812 3 31 Q218 Highend 201901 4 35 Q218 Highend 201902 5 40 Q318 Highend 201807 2 2 Q318 Highend 201808 1 3 Q318 Highend 201809 3 6 Q318 Highend 201810 6 12 Q318 Highend 201811 4 16 Q318 Highend 201812 3 19 Q318 Highend 201901 10 29 Q318 Highend 201902 2 31 Q318 Highend 201903 4 35 Q318 Highend 201904 10 45 Q318 Highend 201905 2 47 Q318 Highend 201906 4 51 Q218 Lowend 201807 7 7 Q218 Lowend 201808 8 15 Q218 Lowend 201809 8 23 Q218 Lowend 201810 3 26 Q218 Lowend 201811 1 27 Q218 Lowend 201812 6 33 Q218 Lowend 201901 1 34 Q218 Lowend 201902 8 42 Q318 Lowend 201810 10 10 Q318 Lowend 201811 4 14 Q318 Lowend 201812 2 16 Q318 Lowend 201901 2 18 Q318 Lowend 201902 6 24 Q318 Lowend 201903 9 33 Q318 Lowend 201904 9 42 Q318 Lowend 201905 6 48 Q318 Lowend 201906 6 54
1 ACCEPTED SOLUTION
Resolver V

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

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

Announcements

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

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors