cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Miskondukt Member
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:

BudgetTbl.png

 

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

 

tblMonthlyBudget.pngIgnore 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.

ExampleRB.png

 


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.

GoodTale.png

 

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
    AddFY
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

 

MappingMonthBudgetHQPnLCatDaily Budget
New Gear Sls1/1/2018    164,448Washington DCRevenue            13,704
New Gear Sls2/1/2018    114,592Washington DCRevenue               9,549
New Gear Sls3/1/2018    316,387Washington DCRevenue            26,366
New Gear Sls4/1/2018    157,238Washington DCRevenue            13,103
New Gear Sls5/1/2018    151,227Washington DCRevenue            12,602
New Gear Sls6/1/2018    129,591Washington DCRevenue            10,799
New Gear Sls7/1/2018    202,872Washington DCRevenue            16,906
New Gear Sls8/1/2018    423,966Washington DCRevenue            35,331
New Gear Sls9/1/2018    221,662Washington DCRevenue            18,472
New Gear Sls10/1/2018    390,289Washington DCRevenue            32,524
New Gear Sls11/1/2018    195,630Washington DCRevenue            16,303
New Gear Sls12/1/2018    282,097Washington DCRevenue            23,508

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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:

 

daily budget.png

 

See also attach PBIX file

 

Regards,

MFelix



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

Proud to be a Datanaut!




22 REPLIES 22
Miskondukt Member
Member

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

Data from example visualization (manually calc'd)

 

DateCorrectWrongCorrectRBWrongRB

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

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

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

Miskondukt Member
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
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.

Miskondukt Member
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
Super User

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

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

Miskondukt Member
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.