Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshua1990
Post Prodigy
Post Prodigy

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

@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.

 

Greg_Deckler
Super User
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors