Cumulative sum with calculated measure as field in power BI desktop

[Anonymous]

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.

[v-lionel-msft]

Hi @Anonymous ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

In your Running Net Sales measure, you are using max(VW_RPT_SWGLINTD[Date Picker]). How do you visualize this data? Are you using a table visual with VW_RPT_SWGLINTD[Date Picker] in one column and the Running Net Sales measure?

Please share the screenshot of the report if possible to understand the context in which you are executing the measure.

[Anonymous]

Hii Fowmy,

Yes, I am using a table with the Day of the Date hierarchy. I am attaching a picture for better reference. I have made the above you measure in this table which I have stated above. Both are giving the same results but I am Running Net sales to give cumulative results.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please try this.

Running Net sales = 
SUMX(
    FILTER(
        ALL(VW_RPT_SWGLINTD),
       [Date Picker] <= MAX([Date Picker])
    ),
    [Netsales]
)

If it's useless, please provide a sample .pbix file(Remove sensitive data.).

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.