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.

New Member

Cumulative sum with calculated measure as field in power BI

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.

1 ACCEPTED SOLUTION
Solution Sage

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

4 REPLIES 4
Solution Sage

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

New Member

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
)
Solution Sage

Hi !

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

Regards,

New Member

Hii,

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

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