Cumulative sum with calculated measure as field in power BI

[vishwasrajput66]

Hello,

I need to find the cumulative sum of my sales but my sales are not a direct field in the table. Its a measure created by me:-

Netsales =((CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]>=445000,VW_RPT_SWGLINTD[NTOFNO]<=445999))+(CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]=449000))+(CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]=450000))+(CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]>=448100,VW_RPT_SWGLINTD[NTOFNO]<=448999))+(CALCULATE(Sum(VW_RPT_SWGLINTD[NTAMVD_NEG]),VW_RPT_SWGLINTD[NTOFNO]>=451000,VW_RPT_SWGLINTD[NTOFNO]<=451999)))

Now I want to use this Net sales measure to get the cumulative sales. I tried:-

Running Net sales = calculate([Netsales]),filter(all(VW_RPT_SWGLINTD),VW_RPT_SWGLINTD[Date Picker <=max(VW_RPT_SWGLINTD[Date Picker])))

But it doesn't work. It just gave the same value as the NetSales. I cannot take Sum([Netsales]) as Netsales is a measure and it gives an error. I need some way to find the sum of a measure that I created. I also tried using the calculated column but it doesn't work either.

[HashamNiaz · Accepted solution]

Hi !

You can get the desired out using following DAX measure;

Running Net Sales = 
CALCULATE(
	[Netsales],
	FILTER(
		ALLSELECTED('Calendar'[Date]),
		ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
	)
)

You can replace the 'Calendar' dimension name with your date table in the DAX formula.

Regards,

Hasham

[vishwasrajput66]

Hii Hasham,

I tried your solution. It didn't work. I created this:-

Running Net Sales =

CALCULATE(

    [Net sales],

    FILTER(

        ALLSELECTED(VW_RPT_SWGLINTD[Date Picker]),

        ISONORAFTER(VW_RPT_SWGLINTD[Date Picker], MAX(VW_RPT_SWGLINTD[Date Picker]), DESC)

    )

I have uploaded the result below, NetSales and running net sales are still same.

Regards,

Vishwas

)

[HashamNiaz]

Hi !

Try to use the VW_RPT_SWGLINTD[Date Picker] column in your matrix.

Regards,

[vishwasrajput66]

Hii,

I was using VW_RPT_SWGLINTD[Date Picker] as a hierarchy. When I used it as a single dimension. It worked. thank you