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
Miskondukt
Helper III
Helper III

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:

 

Ignore red highlightsIgnore 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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

22 REPLIES 22
Miskondukt
Helper III
Helper III

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

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?

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Why are you so upset with the facts? I posted the data, the question, and my expected results from the get-go. You simply skipped over it and are blaming me for it.

Hi,

 

Download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I'm sorry to tell you, sir, but that's not what I'm looking for. To repeat what I originally posted, I included that table of the daily dates and amounts ONLY as an example of the output-"I have also included the expected output EXAMPLE data, but I want to create a measure rather than create a table to upload."

 

I have 11 locations nationally, and six revenue streams per location. This is why I am trying to create a measure rather than a table, OR, is that the ONLY way this works?

Hi,

 

I do not understand your input/output.  Perhaps someone else who does will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I will regather my thoughts and try another explanation post in the AM. Thanks for trying Ashish

Hi @Miskondukt,

 

First of all sorry for saying this but @Ashish_Mathur is only trying to help you, he is a great datanaut and helped a lots of persons in this forum, if you are asking for help and people don't understand your questions you should not be upset since they are using they own time to help you without any return so just be nice to the people that are helping you especcially with people like @Ashish_Mathur and @Greg_Deckler, that use all their spare time to help people that they don't know.

 

Having said this I believe that the problem is on your dataset I have look at the information and somethings are not correct:

 

  • Your Daily budget for all the months appears to be divided by 12 and not by the total days in the month
  • The annual average daily is divided by the 365 days in the year

 

This 2 incoerent data will not give you the expected result if you want daily values tihs should be divided by the totals in one month and not by 12.

 

Please get back on this and confirm your daily values so that I can have a measure to help you out.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you for your support and kind words @MFelix


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Morning Felix,

My dataset, not those tables I provided as mere visual examples of the intended measure output, is a simple table of monthly budgets for various revenue segments (see below).

 

  • The "Daily Budget" (from my OP) column was a manually add for the visual explanation and
  • The table of the daily amounts listing every day and the amount in my own replies was a visual only. In trying to explain what I'm needing, I'm showing how it is intended to compute; however, I specifically stated numerous times that I am needing to create a measure rather than calc'd columns.

This is how my organic table looks (I have Rev and COSTS budgets, so adding columns is too data heavy for every.single.day)

 

MappingMonthBudgetHQPnLCatRental Type

New Gear Sls1/1/2018    164,448Washington DCRevenue 
New Gear Sls2/1/2018    114,592Washington DCRevenue 
New Gear Sls3/1/2018    316,387Washington DCRevenue 
New Gear Sls4/1/2018    157,238Washington DCRevenue 
New Gear Sls5/1/2018    151,227Washington DCRevenue 
New Gear Sls6/1/2018    129,591Washington DCRevenue 
New Gear Sls7/1/2018    202,872Washington DCRevenue 
New Gear Sls8/1/2018    423,966Washington DCRevenue 
New Gear Sls9/1/2018    221,662Washington DCRevenue 
New Gear Sls10/1/2018    390,289Washington DCRevenue 
New Gear Sls11/1/2018    195,630Washington DCRevenue 
New Gear Sls12/1/2018    282,097Washington DCRevenue 
Used Gear Sls1/1/2018    130,713Washington DCRevenue 
Used Gear Sls2/1/2018    147,824Washington DCRevenue 
Used Gear Sls3/1/2018    155,203Washington DCRevenue 
Used Gear Sls4/1/2018    186,439Washington DCRevenue 
Used Gear Sls5/1/2018    192,013Washington DCRevenue 
Used Gear Sls6/1/2018    194,823Washington DCRevenue 
Used Gear Sls7/1/2018      48,450Washington DCRevenue 
Used Gear Sls8/1/2018    196,070Washington DCRevenue 
Used Gear Sls9/1/2018    156,716Washington DCRevenue 
Used Gear Sls10/1/2018      82,188Washington DCRevenue 
Used Gear Sls11/1/2018    104,585Washington DCRevenue 
Used Gear Sls12/1/2018    154,975Washington DCRevenue 

 

I'm not wanting to create calculated columns within a table, I want to save the RAM and create a measure from that data table (I have 11 locations, up to six revenue segments per location) that calculates the running budget balance based on the context's month's budget and not the SUM of all budgets / 365 or the number of days in context as is the current meausure I provided is doing.

 

I understand the measure I provided does / 365, I wrote that in there after not being able to get the intended measure to work due to not knowing how to format the measure and/or variables (I just bought the M$ Definitive DAX book last night).

 

This is, again, why I provided the expected output both in a table and GRAPH because, well, Greg's "How to" post suggested this.

 

Crap, I again forgot the relationships image (now included). Ignore the left side, this is a draft build for the final one, so they're now unused tables (those crossed with the red X, that is)

Model.png

I am also sorry for saying this, but your people bounced me back and forth on format. My frustration had come from first having asked this question like many, many others do on this forum-Simply. I was told don't do that, desptie seeing posts getting responded to w/ much, much less than what I initially posted. So, I followed advice and went detailed. Then I was told don't do that, make it simple and add data (while the data had already been there).

 

So yes, I completely understand they and you are here to help, voluntarily (I am quite active on an Excel forum). I came here FOR that help but got bounced around like a pinball instead (perhaps a little dramatic 8 ] ), on format all while content and context was disregarded. I sincerely do appreciate you considering their POV but, honestly, theirs aren't the only ones.

As usual, please let me know if I can provide any additional information to better explain what this measure is trying to calc.
Thank you

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you much, this did it. I see how this works too, apprecaite the help and patience. I think I'll have a few more tough ones over the weekend I'll be workin on.

Will do me best to convey my goals.

Cools, I will check it out when I jump back on this later this afternoon. Will the dates table I used (provided in the OP) work effectively? It's a function table I picked up from a tutorial site for PBI.

Hi @Miskondukt,

 

This should work with any date table you have, as long as they are linked to the Budget table.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you good sir, am reviewing now.

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.