cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

Cumulative Total for weekly budget

Hello everyone!

I have a budget table with following information:

AreaYear-WeekBudget
A2020-01500
A2020-02550

 

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:

DateBudget per WeekNumber WorkingdaysBudget PhasingBudget RT
01.01.20205004125125
02.01.20205004125125
03.01.20205004125125
04.01.20205004125125
05.01.20205004125125
06.01.20205505110110
07.01.20205505110110
08.01.20205505110110
09.01.20205505110110
10.01.20205505110110
11.01.20205505110110
12.01.20205505110110
13.01.20206005120120
14.01.20206005120120
15.01.20206005120120
16.01.20206005120120
17.01.20206005120120
18.01.20206005120120
19.01.20206005120120

 

But I need something like this:

DateWeekdayBudget per WeekNumber WorkingdaysBudget PhasingBudget RT
01.01.202035004125125
02.01.202045004125250
03.01.202055004125375
04.01.202065004125375
05.01.202075004125375
06.01.202015505110485
07.01.202025505110595
08.01.202035505110705
09.01.202045505110815
10.01.202055505110925
11.01.202065505110925
12.01.202075505110925
13.01.2020160051201045
14.01.2020260051201165
15.01.2020360051201285
16.01.2020460051201405
17.01.2020560051201525
18.01.2020660051201525
19.01.2020760051201525

 

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

 

His is this possible with a DAX measure?

Please, no Power Query.

4 REPLIES 4
Highlighted
Super User IV
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])


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
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://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
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...


Appreciate your Kudos.

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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