cancel
Showing results for
Did you mean:
Highlighted Helper I

## Need help formatting data structure.

Hi so I have a data source that gives a sales persons numbers in this format:

 Date Sale amount 4/1/20 \$100 6/1/20 \$200 6/3/20 \$200

I need to be able to format the data so it becomes a cumulative total and includes all the months up to the current month.

 Date Sales Amount (Cumulative Total) 4/1/20 \$100 5/1/20 \$100 6/1/20 \$500 7/1/20 \$500 8/1/20 \$500

Any ideas on how to create a data table that has this structure?

Thanks as always!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User VI

## Re: Need help formatting data structure.

first create a calendar table

``Calendar = CALENDARAUTO()``

then you can create whether a calculated column

``````Sales Amount (Cumulative Total) =
CALCULATE(SUM(Table[Sale amount]), FILTER(ALL(Table]), Table[Date] <= EARLIER(Calendar[Date])))``````

or measure

``````Sales Amount (Cumulative Total) =
CALCULATE(SUM(Table[Sale amount]), FILTER(ALL(Table]), Table[Date] <= MAX(Calendar[Date])))``````

for this calendar table

do not hesitate to give a kudo to useful posts and mark solutions as solution
5 REPLIES 5
Highlighted Super User VI

## Re: Need help formatting data structure.

first create a calendar table

``Calendar = CALENDARAUTO()``

then you can create whether a calculated column

``````Sales Amount (Cumulative Total) =
CALCULATE(SUM(Table[Sale amount]), FILTER(ALL(Table]), Table[Date] <= EARLIER(Calendar[Date])))``````

or measure

``````Sales Amount (Cumulative Total) =
CALCULATE(SUM(Table[Sale amount]), FILTER(ALL(Table]), Table[Date] <= MAX(Calendar[Date])))``````

for this calendar table

do not hesitate to give a kudo to useful posts and mark solutions as solution
Highlighted Super User IX

## Re: Need help formatting data structure.

@grantculp , you have to create measure like, with a date table

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Proud to be a Super User!

Highlighted Helper I

## Re: Need help formatting data structure.

This is very close with the measure but it is giving an unexpected result. It is assigning the total sum of that column to a every person. Not a cumulative total of each person for each month. Highlighted Memorable Member

## Re: Need help formatting data structure.

here is a tutorial on running totals

Highlighted Super User VI

## Re: Need help formatting data structure.

do you have relationships between calendar table and sales table?

what table (calendar or sales) do you use as date columns in visual?

do not hesitate to give a kudo to useful posts and mark solutions as solution  