cancel
Showing results for
Did you mean:
Highlighted
Member

## Measure for A Budget Running Balance from Varying Monthly Budgets

Lo All,

I have a non-static budget that I am attempting to create a measure to calclulate a running total based on those monthly budgets (see attached image from the data from below for one segment and one location).

Example data:

ASIDE: I pressume that w/in the data-modeled environment I can apply filters and the measure will dynamically interact from the modeled relationships:

Ignore red highlights

The issue I am running into is that the measure is calcualting based on an anual total rather than the month's specific amount. Please see the example visualization of where I am trying to end up at.

I have written a measrue from a video I watched, modifying it ofc, but it is calculating the running total from the annual amount.

Part 1

```Budget Allocation =
VAR
DaysinDateContext = CountRows(Dates)
VAR
TotalBudget = CALCULATE([Total Budget],ALLSELECTED(tblMonthlyBudgets[Month]))
RETURN
([Total Budget]/365)*DaysinDateContext```

Part 2

```Running Total-Budget =
CALCULATE([Budget Allocation],
FILTER(ALLSELECTED(Dates),
Dates[Date]<=MAX(Dates[Date])))```

Example image of bad (current measure calc logic) and good data (manually calc'd in Excel) which is also included in the data below. The green area is how the measure should calc the budget.

And the dates table

```let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, "YearNumber", each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, "QuarterOfYear", each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, "MonthOfYear", each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "MonthName", each Date.ToText([Date], "MMMM"), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "MonthInCalendar", each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "QuarterInCalendar", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "DayInWeek", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Date.ToText([Date], "dddd"), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, "WeekEnding", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Number", each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,"MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"QuarternYear", Int64.Type},{"Week Number", Int64.Type},{"Year", type text},{"MonthnYear", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"MonthOfYear", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthInCalendar", type text}, {"QuarterInCalendar", type text}, {"DayInWeek", Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, "ShortYear", each Text.End(Text.From([Year]), 2), type text),
AddFY = Table.AddColumn(InsertShortYear, "FY", each "FY"&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
in
fnDateTable```

I hope that I've explained my needs clearly in the context and the images/data. Let me know if I can provide any additional information.

Thanks peeps

 Mapping Month Budget HQ PnLCat Daily Budget New Gear Sls 1/1/2018 164,448 Washington DC Revenue 13,704 New Gear Sls 2/1/2018 114,592 Washington DC Revenue 9,549 New Gear Sls 3/1/2018 316,387 Washington DC Revenue 26,366 New Gear Sls 4/1/2018 157,238 Washington DC Revenue 13,103 New Gear Sls 5/1/2018 151,227 Washington DC Revenue 12,602 New Gear Sls 6/1/2018 129,591 Washington DC Revenue 10,799 New Gear Sls 7/1/2018 202,872 Washington DC Revenue 16,906 New Gear Sls 8/1/2018 423,966 Washington DC Revenue 35,331 New Gear Sls 9/1/2018 221,662 Washington DC Revenue 18,472 New Gear Sls 10/1/2018 390,289 Washington DC Revenue 32,524 New Gear Sls 11/1/2018 195,630 Washington DC Revenue 16,303 New Gear Sls 12/1/2018 282,097 Washington DC Revenue 23,508

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Hi @Miskondukt,

To what I can understand you want to calculate the total YTD of the daily budget based on the monthly budget is this correct?

To achieve this based on your model you need to have a Date Dimension table then add the following measure to your model:

```Daily YTD Budget =
TOTALMTD (
SUMX (
DimDate;
SUM ( Budget[Budget] ) / DAY ( EOMONTH ( MIN ( DimDate[Date] ); 0 ) )
);
DimDate[Date]
)
+ IF (
MONTH ( MAX ( DimDate[Date] ) ) = 1;
0;
CALCULATE (
TOTALYTD ( SUM ( Budget[Budget] ); DimDate[Date] );
DATEADD ( DimDate[Date]; -1; MONTH )
)
)```

This sould give the expected result:

Regards,

MFelix

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

Proud to be a Datanaut!

22 REPLIES 22
Member

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Data from example visualization (manually calc'd)

DateCorrectWrongCorrectRBWrongRB

 1/1/2018 13704 7,534 \$13,704 \$7,534 1/2/2018 13704 7,534 \$27,408 \$15,068 1/3/2018 13704 7,534 \$41,112 \$22,603 1/4/2018 13704 7,534 \$54,816 \$30,137 1/5/2018 13704 7,534 \$68,520 \$37,671 1/6/2018 13704 7,534 \$82,224 \$45,205 1/7/2018 13704 7,534 \$95,928 \$52,740 1/8/2018 13704 7,534 \$109,632 \$60,274 1/9/2018 13704 7,534 \$123,336 \$67,808 1/10/2018 13704 7,534 \$137,040 \$75,342 1/11/2018 13704 7,534 \$150,744 \$82,877 1/12/2018 13704 7,534 \$164,448 \$90,411 1/13/2018 13704 7,534 \$178,152 \$97,945 1/14/2018 13704 7,534 \$191,856 \$105,479 1/15/2018 13704 7,534 \$205,560 \$113,014 1/16/2018 13704 7,534 \$219,264 \$120,548 1/17/2018 13704 7,534 \$232,968 \$128,082 1/18/2018 13704 7,534 \$246,672 \$135,616 1/19/2018 13704 7,534 \$260,376 \$143,151 1/20/2018 13704 7,534 \$274,080 \$150,685 1/21/2018 13704 7,534 \$287,784 \$158,219 1/22/2018 13704 7,534 \$301,488 \$165,753 1/23/2018 13704 7,534 \$315,192 \$173,288 1/24/2018 13704 7,534 \$328,896 \$180,822 1/25/2018 13704 7,534 \$342,600 \$188,356 1/26/2018 13704 7,534 \$356,304 \$195,890 1/27/2018 13704 7,534 \$370,008 \$203,425 1/28/2018 13704 7,534 \$383,712 \$210,959 1/29/2018 13704 7,534 \$397,416 \$218,493 1/30/2018 13704 7,534 \$411,120 \$226,027 1/31/2018 13704 7,534 \$424,824 \$233,562 2/1/2018 9549.36 7,534 \$434,374 \$241,096 2/2/2018 9549.36 7,534 \$443,923 \$248,630 2/3/2018 9549.36 7,534 \$453,472 \$256,164 2/4/2018 9549.36 7,534 \$463,022 \$263,699 2/5/2018 9549.36 7,534 \$472,571 \$271,233 2/6/2018 9549.36 7,534 \$482,120 \$278,767 2/7/2018 9549.36 7,534 \$491,670 \$286,301 2/8/2018 9549.36 7,534 \$501,219 \$293,836 2/9/2018 9549.36 7,534 \$510,768 \$301,370 2/10/2018 9549.36 7,534 \$520,318 \$308,904 2/11/2018 9549.36 7,534 \$529,867 \$316,438 2/12/2018 9549.36 7,534 \$539,417 \$323,973 2/13/2018 9549.36 7,534 \$548,966 \$331,507 2/14/2018 9549.36 7,534 \$558,515 \$339,041 2/15/2018 9549.36 7,534 \$568,065 \$346,575 2/16/2018 9549.36 7,534 \$577,614 \$354,110 2/17/2018 9549.36 7,534 \$587,163 \$361,644 2/18/2018 9549.36 7,534 \$596,713 \$369,178 2/19/2018 9549.36 7,534 \$606,262 \$376,712 2/20/2018 9549.36 7,534 \$615,812 \$384,247 2/21/2018 9549.36 7,534 \$625,361 \$391,781 2/22/2018 9549.36 7,534 \$634,910 \$399,315 2/23/2018 9549.36 7,534 \$644,460 \$406,849 2/24/2018 9549.36 7,534 \$654,009 \$414,384 2/25/2018 9549.36 7,534 \$663,558 \$421,918 2/26/2018 9549.36 7,534 \$673,108 \$429,452 2/27/2018 9549.36 7,534 \$682,657 \$436,986 2/28/2018 9549.36 7,534 \$692,206 \$444,521
Member

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

 3/1/2018 26365.6 7,534 \$718,572 \$452,055 3/2/2018 26365.6 7,534 \$744,938 \$459,589 3/3/2018 26365.6 7,534 \$771,303 \$467,123 3/4/2018 26365.6 7,534 \$797,669 \$474,658 3/5/2018 26365.6 7,534 \$824,034 \$482,192 3/6/2018 26365.6 7,534 \$850,400 \$489,726 3/7/2018 26365.6 7,534 \$876,765 \$497,260 3/8/2018 26365.6 7,534 \$903,131 \$504,795 3/9/2018 26365.6 7,534 \$929,497 \$512,329 3/10/2018 26365.6 7,534 \$955,862 \$519,863 3/11/2018 26365.6 7,534 \$982,228 \$527,397 3/12/2018 26365.6 7,534 \$1,008,593 \$534,932 3/13/2018 26365.6 7,534 \$1,034,959 \$542,466 3/14/2018 26365.6 7,534 \$1,061,324 \$550,000 3/15/2018 26365.6 7,534 \$1,087,690 \$557,534 3/16/2018 26365.6 7,534 \$1,114,056 \$565,068 3/17/2018 26365.6 7,534 \$1,140,421 \$572,603 3/18/2018 26365.6 7,534 \$1,166,787 \$580,137 3/19/2018 26365.6 7,534 \$1,193,152 \$587,671 3/20/2018 26365.6 7,534 \$1,219,518 \$595,205 3/21/2018 26365.6 7,534 \$1,245,884 \$602,740 3/22/2018 26365.6 7,534 \$1,272,249 \$610,274 3/23/2018 26365.6 7,534 \$1,298,615 \$617,808 3/24/2018 26365.6 7,534 \$1,324,980 \$625,342 3/25/2018 26365.6 7,534 \$1,351,346 \$632,877 3/26/2018 26365.6 7,534 \$1,377,711 \$640,411 3/27/2018 26365.6 7,534 \$1,404,077 \$647,945 3/28/2018 26365.6 7,534 \$1,430,443 \$655,479 3/29/2018 26365.6 7,534 \$1,456,808 \$663,014 3/30/2018 26365.6 7,534 \$1,483,174 \$670,548 3/31/2018 26365.6 7,534 \$1,509,539 \$678,082 4/1/2018 13103.1 7,534 \$1,522,642 \$685,616 4/2/2018 13103.1 7,534 \$1,535,746 \$693,151 4/3/2018 13103.1 7,534 \$1,548,849 \$700,685 4/4/2018 13103.1 7,534 \$1,561,952 \$708,219 4/5/2018 13103.1 7,534 \$1,575,055 \$715,753 4/6/2018 13103.1 7,534 \$1,588,158 \$723,288 4/7/2018 13103.1 7,534 \$1,601,261 \$730,822 4/8/2018 13103.1 7,534 \$1,614,364 \$738,356 4/9/2018 13103.1 7,534 \$1,627,467 \$745,890 4/10/2018 13103.1 7,534 \$1,640,571 \$753,425 4/11/2018 13103.1 7,534 \$1,653,674 \$760,959 4/12/2018 13103.1 7,534 \$1,666,777 \$768,493 4/13/2018 13103.1 7,534 \$1,679,880 \$776,027 4/14/2018 13103.1 7,534 \$1,692,983 \$783,562 4/15/2018 13103.1 7,534 \$1,706,086 \$791,096 4/16/2018 13103.1 7,534 \$1,719,189 \$798,630 4/17/2018 13103.1 7,534 \$1,732,292 \$806,164 4/18/2018 13103.1 7,534 \$1,745,396 \$813,699 4/19/2018 13103.1 7,534 \$1,758,499 \$821,233 4/20/2018 13103.1 7,534 \$1,771,602 \$828,767 4/21/2018 13103.1 7,534 \$1,784,705 \$836,301 4/22/2018 13103.1 7,534 \$1,797,808 \$843,836 4/23/2018 13103.1 7,534 \$1,810,911 \$851,370 4/24/2018 13103.1 7,534 \$1,824,014 \$858,904 4/25/2018 13103.1 7,534 \$1,837,117 \$866,438 4/26/2018 13103.1 7,534 \$1,850,221 \$873,973 4/27/2018 13103.1 7,534 \$1,863,324 \$881,507 4/28/2018 13103.1 7,534 \$1,876,427 \$889,041 4/29/2018 13103.1 7,534 \$1,889,530 \$896,575 4/30/2018 13103.1 7,534 \$1,902,633 \$904,110
Member

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Hey @Ashish_Mathur, I see you posting a lot this evening since I've uploaded my question. I've included my measures, example data and my expected results via both a manually calculated-included below-data table and a graphical representation.

Any ideas on how I can knock out this measure? Need any additional information?

Super User

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Hi,

Let's keep things very simple.  Share your source dataset and the expected result there.  I am not interested in any graphs.  JUst share the input and your expected ouput.  Share data such that i can paste in an Excel file.

Member

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Thanks for taking a look, Ashish, but when I tried simple your colleague Greg Deckler scolded me to be detailed after pointing me to his "How To" article for 'posting', which I did exactly that in this post. Now I'm being told be simple.

How else does one upload data on here if not per your colleague's own article? https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490...

I have also included the expected output EXAMPLE data, but I want to create a measure rather than create a table to upload.

The graph was one part of my long, detailed post. That wasn't intended to be the focal point, at all. Oh, and I did just test-copy/paste the data I included in the original post and it pasted just fine into Excel.

Super User

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Hi,

Let's just focus on solving the problem.  I am really not concerned with comments posted by other contributors.  I am sue they are absolutely correct in their own right and so are you.   For me to help you, please share the data requested above.

Member

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Sir, I am not sure what I've done to make you respond so curtly, but The.Data.Is.There. in the original post.

Super User

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Which part of my sentence out there sounds curt/rude?

Member

## Re: Measure for A Budget Running Balance from Varying Monthly Budgets

Is it rude to not listen to someone when speaking with them then tell them that they're in the wrong when you weren't even listening?

By ignoring the context of my question's content, you committed that same scenario in this environment. Your first post was ignorant of the time I spent to write a detailed post AS PER THIS FORUM's OWN BENCHMARK post (that you also said you didn't care about), you didn't even bother to read my post but apparently scrolled through it, saw a graph and groaned replying that you "aren't interested in any graphs" when that wasn't even in any way the focus of the post-at all-and then told me to upload data; data that had you read the post you would had seen me not only mention mine including it but also the actual included data (AS PER THIS FORUM's OWN BENCHMARK post).

That's not to mention that I had to ASK you to look at my question AFTER watching you reply to others for over 20 minutes of which most were NEW posts posted AFTER my own. This I am not so much bothered by, it's merely appropraite context.

Then, again you replied a second time telling me to post my data after I already had in the original post which I responded to you again telling you just that.

Now, I wholly don't exepct any help but principles are principles. I substantiated myself.