cancel
Showing results for
Did you mean:
Highlighted
Post Partisan

## Cumulative Total for weekly budget

Hello everyone!

I have a budget table with following information:

 Area Year-Week Budget A 2020-01 500 A 2020-02 550

This table has a relationship with our item master (Area) and the calendar table (Year-Week).

Now I have determined the daily Budget based on the number of working days per week.

The measure looks like this:

``Budget Phasing Test = DIVIDE([Budget], [# Working Days per Week])``

This works pretty good.

Now I have the budget per day.

Now I would like to determine the cumulative budget target till today.

How is this possible?

For now it is like this:

 Date Budget per Week Number Workingdays Budget Phasing Budget RT 01.01.2020 500 4 125 125 02.01.2020 500 4 125 125 03.01.2020 500 4 125 125 04.01.2020 500 4 125 125 05.01.2020 500 4 125 125 06.01.2020 550 5 110 110 07.01.2020 550 5 110 110 08.01.2020 550 5 110 110 09.01.2020 550 5 110 110 10.01.2020 550 5 110 110 11.01.2020 550 5 110 110 12.01.2020 550 5 110 110 13.01.2020 600 5 120 120 14.01.2020 600 5 120 120 15.01.2020 600 5 120 120 16.01.2020 600 5 120 120 17.01.2020 600 5 120 120 18.01.2020 600 5 120 120 19.01.2020 600 5 120 120

But I need something like this:

 Date Weekday Budget per Week Number Workingdays Budget Phasing Budget RT 01.01.2020 3 500 4 125 125 02.01.2020 4 500 4 125 250 03.01.2020 5 500 4 125 375 04.01.2020 6 500 4 125 375 05.01.2020 7 500 4 125 375 06.01.2020 1 550 5 110 485 07.01.2020 2 550 5 110 595 08.01.2020 3 550 5 110 705 09.01.2020 4 550 5 110 815 10.01.2020 5 550 5 110 925 11.01.2020 6 550 5 110 925 12.01.2020 7 550 5 110 925 13.01.2020 1 600 5 120 1045 14.01.2020 2 600 5 120 1165 15.01.2020 3 600 5 120 1285 16.01.2020 4 600 5 120 1405 17.01.2020 5 600 5 120 1525 18.01.2020 6 600 5 120 1525 19.01.2020 7 600 5 120 1525

So I get a running total for working day (1-5) based on the "Budget Phasing".

His is this possible with a DAX measure?

4 REPLIES 4
Highlighted
Super User IV

## Re: Cumulative Total for weekly budget

@joshua1990 - Yes, there is a running total quick measure that you can use. More or less it is:

VAR __Date = MAX('Table'[Date])

RETURN

SUMX(FILTER('Table',[Date]<=__Date),[Column])

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Super User IV

## Re: Cumulative Total for weekly budget

@joshua1990 , with date calendar

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

But if want total to reset every year

``````YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
``````

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
Post Partisan

## Re: Cumulative Total for weekly budget

@Greg_Deckler : Thanks, but it's not working! I get a false value everytime.

Are you sure this approach works with a measure [Budget], that is the result from a DIVIDE?

Especially when it comes to non-weekday your approach ist not working.

Highlighted
Super User IV

## Re: Cumulative Total for weekly budget

@joshua1990 - It's the same basic DAX but in the case that you want to perform a cumulative sum of a measure, then you will need to create a table variable using SUMMARIZE for example that emulates what you displayed as your data. You then do the SUMX thing across that table, which you filter, so along the lines of:

Measure =

VAR __Date = MAX('Table'[Date])

VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Date]<__Date),[Month],"Measure",[Measure])

RETURN

SUMX(__Table,[Measure])

This is psuedo code since I don't know exactly what your data looks like.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors