Cumulative sum

[Abdel]

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]).

can someone help me please 

[sevenhills · Accepted solution]

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!

[Ashish_Mathur]

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.

[sevenhills · Accepted solution]

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!

[Abdel]

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 

[sevenhills]

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 ]
) 

[Abdel]

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 

[Abdel]

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 🙂  

[sevenhills · Accepted solution]

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

[lbendlin]

Read about COALESCE

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

Filter by week etc.