Cumulative sum of sales in a financial year

[Anonymous]

Hi,

I am trying to include a column for cumulative sum in Power BI table visual. I have the fields of Financial Year, Month and date available also the sales made per month. Can anyone please help me out on how to include an additional column for a cumulative sum.

My power Bi is extracting data from analysis services (Tabular model)

Thanks,

Akshay

[edhans · Accepted solution]

You need a date table to really do this correctly, though it is possible without one. This assumes your date table and field is Date[Date]. 

[Total Sales] below is simply SUM(Sales[Sales])

Cumulative Total = 
VAR CurrentDate = MAX(Sales[Date])
RETURN
CALCULATE(
    [Total Sales],
    REMOVEFILTERS('Date'[Date]),
    'Date'[Date] <= CurrentDate
)

A sample file is at this link.

[Anonymous]

Thanks @edhans  that worked out for me

[edhans]

Great. Glad to help!

[amitchandak]

You have the option to show running a total of filed. You can use that. Also, you can create a quick measure for that.

Or try a formula like this

  
till date = 
Var _max_date = CALCULATE(maxx(all('Date'[Date Filer]),'Date'[Date Filer]))

Var   Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))
Var   Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),Sales[Sales Date]<=_max_date)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Hi Amit,

I applied the following formula in the measure:

CUMSUM =

Var _max_date = CALCULATE(maxx(all(ProjectStartDate[Date]),ProjectStartDate[Date]))

Var Sale_till_tody =CALCULATE(sum(FactProject[SalesPrice]),filter(ProjectStartDate,ProjectStartDate[Date<=MAXX(ProjectStartDate,ProjectStartDate[Date])))

Var Sale_till_tody =CALCULATE(sum(FactProject[SalesPrice]),ProjectStartDate[Date]<=_max_date)

It is throwing me the error for Var _max_date. Can you please let me know if I am putting some wrong values in as I am not familiar with DAX.

Also I tried using the built in quick measure for YTD total bu the filter value of date is not compatible with the Power BI date calendarformat.

[jhartranft60]

@Anonymous  Can you provide a quick mock-up with sample data so that we can better visualize what you're working with along with what you'd like the output to be?  

[Anonymous]

@jhartranft60  Please find the sample data below: