topic Re: Cumulative Total for weekly budget in DAX Commands and Tips

Cumulative Total for weekly budget

joshua1990 — Thu, 06 Aug 2020 13:55:32 GMT

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?

Please, no Power Query.

Re: Cumulative Total for weekly budget

Greg_Deckler — Thu, 06 Aug 2020 14:31:31 GMT

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])

Re: Cumulative Total for weekly budget

amitchandak — Thu, 06 Aug 2020 14:34:28 GMT

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

Appreciate your Kudos.

Re: Cumulative Total for weekly budget

joshua1990 — Thu, 06 Aug 2020 16:26:56 GMT

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.

Re: Cumulative Total for weekly budget

Greg_Deckler — Thu, 06 Aug 2020 17:21:05 GMT

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.