cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
Community Champion

Hi @Anonymous

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
Super User

@Anonymous , 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Community Champion

Hi @Anonymous

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
Anonymous
Not applicable

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.

Community Champion

@Anonymous

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
Memorable Member

here is a tutorial on running totals

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.