The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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:
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 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
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 |
Solved! Go to 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:
See also attach PBIX file
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português3/1/2018 | 26365.57 | 7,534 | $718,572 | $452,055 |
3/2/2018 | 26365.57 | 7,534 | $744,938 | $459,589 |
3/3/2018 | 26365.57 | 7,534 | $771,303 | $467,123 |
3/4/2018 | 26365.57 | 7,534 | $797,669 | $474,658 |
3/5/2018 | 26365.57 | 7,534 | $824,034 | $482,192 |
3/6/2018 | 26365.57 | 7,534 | $850,400 | $489,726 |
3/7/2018 | 26365.57 | 7,534 | $876,765 | $497,260 |
3/8/2018 | 26365.57 | 7,534 | $903,131 | $504,795 |
3/9/2018 | 26365.57 | 7,534 | $929,497 | $512,329 |
3/10/2018 | 26365.57 | 7,534 | $955,862 | $519,863 |
3/11/2018 | 26365.57 | 7,534 | $982,228 | $527,397 |
3/12/2018 | 26365.57 | 7,534 | $1,008,593 | $534,932 |
3/13/2018 | 26365.57 | 7,534 | $1,034,959 | $542,466 |
3/14/2018 | 26365.57 | 7,534 | $1,061,324 | $550,000 |
3/15/2018 | 26365.57 | 7,534 | $1,087,690 | $557,534 |
3/16/2018 | 26365.57 | 7,534 | $1,114,056 | $565,068 |
3/17/2018 | 26365.57 | 7,534 | $1,140,421 | $572,603 |
3/18/2018 | 26365.57 | 7,534 | $1,166,787 | $580,137 |
3/19/2018 | 26365.57 | 7,534 | $1,193,152 | $587,671 |
3/20/2018 | 26365.57 | 7,534 | $1,219,518 | $595,205 |
3/21/2018 | 26365.57 | 7,534 | $1,245,884 | $602,740 |
3/22/2018 | 26365.57 | 7,534 | $1,272,249 | $610,274 |
3/23/2018 | 26365.57 | 7,534 | $1,298,615 | $617,808 |
3/24/2018 | 26365.57 | 7,534 | $1,324,980 | $625,342 |
3/25/2018 | 26365.57 | 7,534 | $1,351,346 | $632,877 |
3/26/2018 | 26365.57 | 7,534 | $1,377,711 | $640,411 |
3/27/2018 | 26365.57 | 7,534 | $1,404,077 | $647,945 |
3/28/2018 | 26365.57 | 7,534 | $1,430,443 | $655,479 |
3/29/2018 | 26365.57 | 7,534 | $1,456,808 | $663,014 |
3/30/2018 | 26365.57 | 7,534 | $1,483,174 | $670,548 |
3/31/2018 | 26365.57 | 7,534 | $1,509,539 | $678,082 |
4/1/2018 | 13103.13 | 7,534 | $1,522,642 | $685,616 |
4/2/2018 | 13103.13 | 7,534 | $1,535,746 | $693,151 |
4/3/2018 | 13103.13 | 7,534 | $1,548,849 | $700,685 |
4/4/2018 | 13103.13 | 7,534 | $1,561,952 | $708,219 |
4/5/2018 | 13103.13 | 7,534 | $1,575,055 | $715,753 |
4/6/2018 | 13103.13 | 7,534 | $1,588,158 | $723,288 |
4/7/2018 | 13103.13 | 7,534 | $1,601,261 | $730,822 |
4/8/2018 | 13103.13 | 7,534 | $1,614,364 | $738,356 |
4/9/2018 | 13103.13 | 7,534 | $1,627,467 | $745,890 |
4/10/2018 | 13103.13 | 7,534 | $1,640,571 | $753,425 |
4/11/2018 | 13103.13 | 7,534 | $1,653,674 | $760,959 |
4/12/2018 | 13103.13 | 7,534 | $1,666,777 | $768,493 |
4/13/2018 | 13103.13 | 7,534 | $1,679,880 | $776,027 |
4/14/2018 | 13103.13 | 7,534 | $1,692,983 | $783,562 |
4/15/2018 | 13103.13 | 7,534 | $1,706,086 | $791,096 |
4/16/2018 | 13103.13 | 7,534 | $1,719,189 | $798,630 |
4/17/2018 | 13103.13 | 7,534 | $1,732,292 | $806,164 |
4/18/2018 | 13103.13 | 7,534 | $1,745,396 | $813,699 |
4/19/2018 | 13103.13 | 7,534 | $1,758,499 | $821,233 |
4/20/2018 | 13103.13 | 7,534 | $1,771,602 | $828,767 |
4/21/2018 | 13103.13 | 7,534 | $1,784,705 | $836,301 |
4/22/2018 | 13103.13 | 7,534 | $1,797,808 | $843,836 |
4/23/2018 | 13103.13 | 7,534 | $1,810,911 | $851,370 |
4/24/2018 | 13103.13 | 7,534 | $1,824,014 | $858,904 |
4/25/2018 | 13103.13 | 7,534 | $1,837,117 | $866,438 |
4/26/2018 | 13103.13 | 7,534 | $1,850,221 | $873,973 |
4/27/2018 | 13103.13 | 7,534 | $1,863,324 | $881,507 |
4/28/2018 | 13103.13 | 7,534 | $1,876,427 | $889,041 |
4/29/2018 | 13103.13 | 7,534 | $1,889,530 | $896,575 |
4/30/2018 | 13103.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.
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.
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?
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.
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.
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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you for your support and kind words @MFelix
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).
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 Sls | 1/1/2018 | 164,448 | Washington DC | Revenue | |
New Gear Sls | 2/1/2018 | 114,592 | Washington DC | Revenue | |
New Gear Sls | 3/1/2018 | 316,387 | Washington DC | Revenue | |
New Gear Sls | 4/1/2018 | 157,238 | Washington DC | Revenue | |
New Gear Sls | 5/1/2018 | 151,227 | Washington DC | Revenue | |
New Gear Sls | 6/1/2018 | 129,591 | Washington DC | Revenue | |
New Gear Sls | 7/1/2018 | 202,872 | Washington DC | Revenue | |
New Gear Sls | 8/1/2018 | 423,966 | Washington DC | Revenue | |
New Gear Sls | 9/1/2018 | 221,662 | Washington DC | Revenue | |
New Gear Sls | 10/1/2018 | 390,289 | Washington DC | Revenue | |
New Gear Sls | 11/1/2018 | 195,630 | Washington DC | Revenue | |
New Gear Sls | 12/1/2018 | 282,097 | Washington DC | Revenue | |
Used Gear Sls | 1/1/2018 | 130,713 | Washington DC | Revenue | |
Used Gear Sls | 2/1/2018 | 147,824 | Washington DC | Revenue | |
Used Gear Sls | 3/1/2018 | 155,203 | Washington DC | Revenue | |
Used Gear Sls | 4/1/2018 | 186,439 | Washington DC | Revenue | |
Used Gear Sls | 5/1/2018 | 192,013 | Washington DC | Revenue | |
Used Gear Sls | 6/1/2018 | 194,823 | Washington DC | Revenue | |
Used Gear Sls | 7/1/2018 | 48,450 | Washington DC | Revenue | |
Used Gear Sls | 8/1/2018 | 196,070 | Washington DC | Revenue | |
Used Gear Sls | 9/1/2018 | 156,716 | Washington DC | Revenue | |
Used Gear Sls | 10/1/2018 | 82,188 | Washington DC | Revenue | |
Used Gear Sls | 11/1/2018 | 104,585 | Washington DC | Revenue | |
Used Gear Sls | 12/1/2018 | 154,975 | Washington DC | Revenue |
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)
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:
See also attach PBIX file
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you good sir, am reviewing now.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
80 | |
78 | |
43 | |
37 |
User | Count |
---|---|
158 | |
111 | |
64 | |
59 | |
54 |