cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Cumulative sum

I have a dataset contains 52 week of sales :

the table below is a sample of the dataset I have.

 Week Sales Actual/forecast 1 10 Actual 1 12 Forecast 2 14 Actual 2 20 Forecast 3 - Actual 3 6 Forecast 4 - Actual 4 8 Forecast

I want to create a visual table in power bi that looks like the below table which basically does the following sum the total actual sales up to the selected week and if there is no actual for the selected week I want to replace it with forecast vale just like roll over column shows.

 Week Actual Forecast Cumulative sum 1 10 12 10 2 14 20 = 10+14 = 24 3 - 6 =24+6=30 _ 8 30+8= 38

I wrote this formula but seems like it only gives me the right output up to week 3 and wrong values after week 3 why is that

test = VAR SelectedWeek = MAX('salesdata[week] VAR ActualSumUpToSelectedWeek = CALCULATE( SUM(salesdata'[Value]), salesdata[actual/forcast] = "actual", salesdata[week] <= SelectedWeek )RETURN ActualSumUpToSelectedWeek + if([ActualSum] = 0, [ForecastSum],[ActualSum]).

2 ACCEPTED SOLUTIONS
Super User

Add this measure or club: I still not understand the intention ...

``````Sales  for Actual =
CALCULATE(
SUM('salesdata'[Sales ]),
'salesdata'[Actual/forecast] IN { "Actual" }
)

Sales  for Forecast =
CALCULATE(
SUM('salesdata'[Sales ]),
'salesdata'[Actual/forecast] IN { "Forecast" }
)

CT Actual or Forecast =
SUMX (
CALCULATETABLE(  values(salesdata[Week ]), salesdata[Week ] <= MAX(salesdata[Week ]))
, COALESCE( salesdata[Sales  for Actual], [Sales  for Forecast])
)
``````

If you dont want multiple measures, you can club the measure logic into one measure!

Hoep this helps!

Super User

I do NOT see any issue.

Check these screenshots:

a) No selection

b) with selection of Week 2, week 2

b) with selection of Week 1, Week 4

8 REPLIES 8
Super User

Hi,

Do you have a Date column?  If no, then do you have Year and Month columns?  If you do have either, then share some data to work with.  Also, share a Calendar table with a Week number column.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Add this measure or club: I still not understand the intention ...

``````Sales  for Actual =
CALCULATE(
SUM('salesdata'[Sales ]),
'salesdata'[Actual/forecast] IN { "Actual" }
)

Sales  for Forecast =
CALCULATE(
SUM('salesdata'[Sales ]),
'salesdata'[Actual/forecast] IN { "Forecast" }
)

CT Actual or Forecast =
SUMX (
CALCULATETABLE(  values(salesdata[Week ]), salesdata[Week ] <= MAX(salesdata[Week ]))
, COALESCE( salesdata[Sales  for Actual], [Sales  for Forecast])
)
``````

If you dont want multiple measures, you can club the measure logic into one measure!

Hoep this helps!

Regular Visitor

Hi I have a question please - I have created two variables minweek and max week and used your formula and added min week so when I select for example week 3 and 6 I want to sum the actuals from week 3 to week 6 and not from week 1 to week 6 but it does not work for some reason

Super User

U have to share the DAX or .pbix for us to understand. 🙂

if u r trying to filter the range of weeks as min and max , you may need to tweak this line as:

``````CALCULATETABLE(  values(salesdata[Week ]),
salesdata[Week ] >= MIN(salesdata[Week ] && salesdata[Week ] <= MAX(salesdata[Week ]
) ``````

Regular Visitor

This is exactly what I did but it does not work I think it's because the data type of my week column. So basically my column contains numbers (1,2,3,4) and the data type is whole decimal number; that's the only reason I think why this formula isn't working for me

Regular Visitor

It works thanks a lot for your help;  quick one please why this formula does not work when selecting multiple weeks from a slicer; say I want to have a table that shows week number, actual, forecast, ct actual if forecasts and use week column in a slicer so I can choose whatever week I want and display the results I also want to have the option to choose mutiple week and still get the results I want following the logic I explained earlier. Thank you 🙂

Super User

I do NOT see any issue.

Check these screenshots:

a) No selection

b) with selection of Week 2, week 2

b) with selection of Week 1, Week 4

Super User

SUMX(<table>,COALESCE([Actual],[Forecast]))

Filter by week etc.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.