Dynamic P&L fiscal year time intelligence for actual vs forecast vs budget vs same period last year
Hi Community, I am trying to create a dynamic time intelligent profit and loss statement comparing actuals to forecast, budget, and the same period last year for each line item. I am using a fiscal year calendar which means the standard time intelligence measures will not work. The comparison amount (column) needs to be the variance between the two and not the amount. For example, net sales, the actual amount is 10,000 and the forecast amount is 12,000. The P&L matrix/table should show 10,000 for the actual amount and (2,000) for the forecast amount. Below is a quick picture of what I am trying to achieve: Line-Item Actual vs Forecast vs Budget vs SPLY Net Sales 10,000 (2,000) (1,000) (3,000) COGS 4,000 2,000 1,000 500 Additionally, I would like to see this on a WTD, MTD, QTD, and YTD basis in one matrix/table. Below is a screenshot of the final product I am trying to create along with the format strings: As you can see, we have four different format strings: whole numbers, whole number dollars, decimal, and percentages. My data model is pictured below: Below is a picture of what I currently have: I have created all the individual line-item measures for actuals, forecast, and budget using the measure branching technique. However, I know this is not the best way to do this because I have three measures for each line item. For example, I have a measure for actual net sales, forecast net sales, and budgeted net sales. As you can see, the forecast, budget, and SPLY measures are not variance amounts. The actual, forecast and budget measures use the SWITCH technique which prevents me from calculating the variance because of the four different format strings and a custom time intelligence function. Below is an example of my Actuals measure: Act HL = SWITCH( [Selected Account], 1, FORMAT([Volume], "#,#;(#,#);-"), 2, FORMAT([Net Sales], "$#,#;($#,#);-"), 3, FORMAT([COGS], "$#,#;($#,#);-"), 4, FORMAT([Gross Margin], "$#,#;($#,#);-"), 5, FORMAT([MAP/MDF], "$#,#;($#,#);-"), 6, FORMAT([SG&A], "$#,#;($#,#);-"), 7, FORMAT([Combined EBIT], "$#,#;($#,#);-"), 9, FORMAT([Net Sales Rate], "$#,0.00;($#,0.00);-"), 10, FORMAT([COGS Rate], "$#,0.00;($#,0.00);-"), 11, FORMAT([Gross Margin Rate], "$#,0.00;($#,0.00);-"), 12, FORMAT([MAP/MDF Rate], "$#,0.00;($#,0.00);-"), 13, FORMAT([SG&A Rate], "$#,0.00;($#,0.00);-"), 14, FORMAT([Combined EBIT Rate], "$#,0.00;($#,0.00);-"), 16, FORMAT([GM as % of Net Sales], "0.0%;-0.0%;-"), 17, FORMAT([Combined EBIT as % of Net Sales], "0.0%;-0.0%;-") ) This measure above is then placed into a time intelligence pattern using the SQLBI DAX Patterns for Week-related calculations. As previously mentioned, I am using a fiscal year calendar so the standard time intelligence measures will not work. For example, the Act WTD column in my matrix above is: Act WTD = IF ( [ShowValueForDates], VAR LastDayOfWeekAvailable = MAX ( 'Date'[Day of Week Number] ) VAR LastFiscalYearWeekAvailable = MAX ( 'Date'[Fiscal Year Week Number] ) VAR Result = CALCULATE ( [Act HL], ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] ), 'Date'[Day of Week Number] <= LastDayOfWeekAvailable, 'Date'[Fiscal Year Week Number] = LastFiscalYearWeekAvailable ) RETURN Result ) The matrix has 16 measures like you see above (actual + forecast + budget + SPLY = 4 x 4 = 16 measures) Obviously this not an efficent or sustainable way to create this. I have a feeling Calculation Groups might help solve the problem. Additionally, when slicers are placed on the page, the query is slow because of the number of measures and the size of the data tables. So community, how can I achieve the above? Thank you for taking the time to read through my question.
Getting Date Filter to filter between monthly and yearly value
I have a table that shows every sale or cancellation for the company. We are filtering this data by date, sales consultant, and/or area. We want to see how monthly/yearly numbers measure vs the budget. Each sales consultant has a different monthly/yearly budget for sales, meetings, sales dollars, and cancellations. I had to manually create a new table and input the four sales consultant along with their appropriate budget. My created table looks something like this: Sales Consultant | Monthly FFF Bud | Yearly FFF Bud | Monthly Sales Dollars Bud | Yearly Sales Dollars Bud | .... Name 1 4.38 52.5 1,312,705 15,752,460 Name 2 2.5 30 785,365 9,424,380 Name 3 1.25 15 362,816 4,353,792 The date range that is connected to a created date table which is connected to the sale/cancel date has this formula: NewSpecialDates = VAR _datetable = NewDateRange VAR _today = TODAY() VAR _month = MONTH(TODAY()) VAR _year = YEAR(TODAY()) VAR _thismonthstart = DATE(_year,_month,1) VAR _thisyearstart = DATE(_year,1,1) VAR _lastmonthstart = EDATE(_thismonthstart,-1) VAR _lastmonthend = _thismonthstart-1 VAR _thisquarterstart = DATE(YEAR(_today),SWITCH(true,_month>9,10,_month>6,7,_month>3,4,1),1) RETURN UNION( ADDCOLUMNS(FILTER(_datetable,[Date]=_today),"Period","Today","Order",1), ADDCOLUMNS(FILTER(_datetable,[Date]=_today-1),"Period","Yesterday","Order",2), ADDCOLUMNS(FILTER(_datetable,[Date]>_today-7),"Period","Last 7 Days","Order",3), ADDCOLUMNS(FILTER(_datetable,[Date]>=_today-14),"Period","Last 2 Weeks","Order",4), ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisquarterstart),"Period","This Quarter","Order",5), ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisyearstart),"Period","This Year","Order",6), ADDCOLUMNS(FILTER(_datetable,[Date]>_today-30),"Period","Last 30 Days","Order",7), ADDCOLUMNS(_datetable,"Period","Custom Range","Order",8) ) I created an IF statement to filter between the monthly and yearly FFF budget but the values do not switch from monthly to yearly. Not sure what I should do next. This is my IF statement: New FFF Bud = IF(SELECTEDVALUE('NewSpecialDates'[Period])="Custom Range",DIVIDE('Budget Table'[Yearly FFF Bud],365)*DATEDIFF(FIRSTDATE('NewSpecialDates'[Date]),LASTDATE('NewSpecialDates'[Date]),DAY),IF(SELECTEDVALUE('NewSpecialDates'[Period])="Last 30 Days",'Budget Table'[Monthly FFF Bud],IF(SELECTEDVALUE(NewSpecialDates[Period])="This Year",'Budget Table'[Monthly FFF Bud]*12,IF(SELECTEDVALUE('NewSpecialDates'[Period])="This Quarter",'Budget Table'[Monthly FFF Bud]*3,IF(SELECTEDVALUE('NewSpecialDates'[Period])="Last 2 Weeks",'Budget Table'[Monthly FFF Bud],IF(SELECTEDVALUE('NewSpecialDates'[Period])="Last 7 Days",'Budget Table'[Monthly FFF Bud],IF(SELECTEDVALUE('NewSpecialDates'[Period])="Today",'Budget Table'[Monthly FFF Bud],IF(SELECTEDVALUE('NewSpecialDates'[Period])="Yesterday",'Budget Table'[Monthly FFF Bud],DIVIDE('Budget Table'[Yearly FFF Bud],12)))))))))
Budget line on waterfall chart
This is more a discussion question than something to be solved. I have been looking for ways to get a budget line into a waterfall chart. I have tried a fair number of custom charts as well as multiple forums. Bottom line Waterfall charts do not support a budget line. (would be happy to be wrong) Am I trying to utilize the chart in a way that it is just not intended to be used? It seems to me that a budget line would be useful. Of course, if the budget is 10,000,000 and the first three months total only 700,000 you have a scale issue. Thank you Bob
Cumulative Budget Allocation Formula With Mismatched Granularities
Hi, I'm currently working on a measure that calculates the cumulative budget allocation with mismatched granularities. I used this Blog by Enterprise DNA to get the formulations of my measures. Also important to note that I've described my problem in more detail in my previous post but the solution created a different issue for me. This is how my data is set up: This is the problem I'm running into: [BudgetAllocation] is fine if I wanted the individual days, but I want it cumulatively. [BudgetAllocation2] was my attempt to make it cumulative, but it really just produced the entire value for the month. [BudgetAllocation3] was another attempt to make it cumulative but because I have the YearMonth = YearMonth, it brings the sum back to zero at the start of each month. My 3 measures are as follows: BudgetAllocation = VAR DayInContext = COUNTROWS('Dates') VAR DaysInMonth = CALCULATE( COUNTROWS( 'Dates' ), ALL( 'Dates' ), VALUES( 'Dates'[Year Month] ) ) VAR CurrentMonth = SELECTEDVALUE( 'Dates'[Year Month] ) VAR MonthlyBudgetAmounts = CALCULATE( [NonlinearBudgetAmt], TREATAS( VALUES ( Dates[Year Month] ), 'Nonlinear Budget Table'[YearMonth] ) ) RETURN IF ( OR( HASONEVALUE( 'Dates'[Date] ), HASONEVALUE ( 'Dates'[Month Year] ) ), DIVIDE ( DayInContext, DaysInMonth, 0 ) * MonthlyBudgetAmounts, [NonlinearBudget]) BudgetAllocation 2 = VAR MaxYearMonth = MAX('Dates'[Year Month]) VAR DayInContext = CALCULATE(COUNTROWS('Dates'), ALL('Dates'),'Dates'[Year Month] = MaxYearMonth ) VAR DaysInMonth = CALCULATE( COUNTROWS( 'Dates' ), ALL( 'Dates' ), VALUES( 'Dates'[Month Year] ) ) VAR CurrentMonth = SELECTEDVALUE( 'Dates'[Year Month] ) VAR MonthlyBudgetAmounts = CALCULATE( [NonlinearBudgetAmt], TREATAS( VALUES ( Dates[Year Month] ), 'Nonlinear Budget Table'[YearMonth] ) ) RETURN IF ( OR( HASONEVALUE( 'Dates'[Date] ), HASONEVALUE ( 'Dates'[Month Year] ) ), DIVIDE ( DayInContext, DaysInMonth, 0 ) * MonthlyBudgetAmounts, [NonlinearBudgetAmt]) BudgetAllocation 3 = VAR MaxDayMonth = MAX('Dates'[Date]) VAR MaxYearMonth = MAX('Dates'[Year Month]) VAR AllDates = ALL(Dates) VAR DayInContext = CALCULATE(COUNTROWS('Dates'), ALL('Dates'),'Dates'[Year Month] = MaxYearMonth && 'Dates'[Date] <= MaxDayMonth ) VAR DaysInMonth = CALCULATE( COUNTROWS( 'Dates' ), ALL( 'Dates' ), VALUES( 'Dates'[Month Year] ) ) VAR CurrentMonth = SELECTEDVALUE( 'Dates'[Year Month] ) VAR MonthlyBudgetAmounts = CALCULATE( [PossSol2], TREATAS( VALUES ( Dates[Year Month] ), 'Nonlinear Budget Table'[YearMonth] ) ) RETURN IF ( OR( HASONEVALUE( 'Dates'[Date] ), HASONEVALUE ( 'Dates'[Month Year] ) ), DIVIDE ( DayInContext, DaysInMonth, 0 ) * MonthlyBudgetAmounts, [NonlinearBudgetAmt]) If anyone knows how to make BudgetAllocation cumulative, let me know. Thanks!Nonlinear Budget Release Measure
Hi, I posted this question a little over a week ago but I don't think I properly articulated what I was looking for. This is the Link. The current data assumes that every [Task] has a linear budget in the [Budget] and [Budget YTD] fields. [Budget] = ( [DaysInDateContext] / [DaysInYear] ) * [TotalBudgetHours] [Budget YTD] = ( [DaysInDateContextYTD] / [DaysInYear] ) * [TotalBudgetHours] *Note: These are imported static values, not measures. These calculations are done prior to importing. However, some Tasks have nonlinear budget releases. An example of this would be: Task X has a total yearly budget of 1,000 hours. Task X receives 12.5% of its budget in the month of July, an additional 12.5% in August, and 75% in September. This means that in January through June, the task has 0 budget hours released. In July it has 125 budget hours, and another 125 budget hours released in August for a total of 250 budget hours. September comes and 750 budget hours is released to that task, for a total of 1,000 hours. Here is a sample of the model I am working with. The five tables we are working with are: Task Table - Tasks are in a hierarchy, this table identifies the hierarchy. Budget Table - Where the budget hours live, by date (DateKey). Dates - What the Budget Table connects to for dates. Linearity Table - Table which dimensions whether the Task & Category are Linear or Nonlinear. Nonlinear Budget Table - Where the nonlinear budget release percentages live. I've provided sample data for these tables below. Some important notes: Linearity depends on both the task and the task category. So a task may be linear in one category budget, but nonlinear in another. The budgets are actually every working day, to simplify it I just used the first, middle and last working day of each month. The nonlinear budget releases are released on a monthly basis. This means there should be a big jump when plotting them by day. ** TO NOT EXCEED CHARACTER COUNT, THE TABLES WILL BE IN REPLIES BELOW ** This is my current DAX measure.. Nonlinear Budget YTD = VAR BudgetHours = CALCULATE ( SUM ( 'Budget Table'[Budget] ), 'Linearity'[Linearity] = "Nonlinear" ) VAR LastTableDate = MAX('Nonlinear Budget'[FirstWorkdayOfMonth]) VAR Budget%YTD = CALCULATE ( SUM ( 'Nonlinear Budget'[Budget%]), ALLEXCEPT ( 'Nonlinear Budget', 'Nonlinear Budget'[Category], 'Nonlinear Budget'[Task] ), 'Nonlinear Budget'[FirstWorkdayOfMonth] <= LastTableDate ) RETURN Budget%YTD * BudgetHours The issue I'm running into is that when I plot it against the date, it's taking the sum of all of the Task & Categories Budget% for that month and muliplying it by the total budget hours. I don't know how to make it calculate every Task & Category separately and then sum those values up. Example: What I'm mainly lookign for is to be able to plot all budgets (nonlinear and linear) on a single graph by date. As shown below. Bonus if we can smooth it so that the full nonlinear budget % gets released gradually throughout the month, and not just on the first day like so: If any additional information is required, I'll respond as quick as I can. Thank you!Forecast future 18 months
Hi All, I am trying to calculate the forecast for this data for the next 18 months. The 18 months need to be dynamic and only when the status is "Active". The forecast figure for the next 18 months is the budget figure divided by 9. This forecast needs to show on a table for the 18 months straight and not just in the total. See the data example below and thanks in advance.Budget Variance - Multiple Slicers
Main Scope The main idea is to create a price variance between the selected reports, within the selected time frames. The user of the dashboard will: 1) Select the current budget and period 2) Select the comparing budget and period Data Setup The sample dataset contains of the following data: The Main Data: - Reports (Categorical variable for the different budget) - DimDate (Date of expected invoicing) - Net Sales - Units Besides, I created two seperate tables that are not connected to the Main Data: Slicer1 = VALUES('Main Data'[Report]) Slicer2 = VALUES('Main Data'[Report]) Current Problem: To create a measure for price variance I will need to create a measure for units and net sales that 1) filters for the selected budget and period in the current period 2) filters the selected budget and period in the comparing period 3) gives the variance of the above selections Initially, I created a measure that filters only the selected budgets based on the selections of the budgets: Unit = VAR units = calculate(sum('Total Data Table'[Invoiced Units]), filter('Total Data Table','Total Data Table'[Report]=SELECTEDVALUE(slicer1[Report]) || 'Total Data Table'[Report]=SELECTEDVALUE(slicer2[Report]))) RETURN if(HASONEVALUE('Total Data Table'[Report]),units, CALCULATE(sum('Total Data Table'[Invoiced Units]), filter('Total Data Table','Total Data Table'[Report]=SELECTEDVALUE(slicer1[Report])))- CALCULATE(sum('Total Data Table'[Invoiced Units]),filter('Total Data Table','Total Data Table'[Report]=SELECTEDVALUE(slicer2[Report])))) This formula does not filter for the selected period, only the selected budgets. I would like to compare different budgets accross different periods. For Example: - Compare Budget 1 in period 01/01/2019 - 12/03/2019 - To Budget 2 in period 01/07/2019 - 12/09/2019 I have tried multiple ways to do this, but have not found any that worked; I hope a smart solutions exists to this issue.Budget Allocations by category but based on different granularity
I am trying to work on a personal finance report and want to incorporate monthly budgets I set for myself. I have created the data model as the following: Transactions Table = Export from Bank transactions outlining day to day transactions with the vendor name Category Mapping = Table that maps Names of vendors to a set amount of category Budget = A set of categories, their budgeted number (monthly) I have a one to many relationship on Vendor with Transactions and Category Mapping and a One to many relationship with Budget and Category mappings on the Category name. So the dataset looks something like this: Transactions: Date | Vendor | Amount Day x | Vendor x | Amount Day x | Vendor z | Amount Day y | Vendor y | Amount Day z | Vendor B | amount Category Mapping Category Name | Vendor Category Name 1 | Vendor x Category Name 2 | Vendor B Category Name 1 | Vendor y Category Name 3 | Vendor z Budget Category Name | Budgeted Category Name 1 | 100 (budget) Category Name 2 | 200 (budget) Category Name 3 | 100 (budget) I am having a really hard time coming up with a measure that would show me the difference in the amount spent vs amount budgeted on a monthly basis. I know the data is on different granularities as the data in transaction is daily (and can have multiple days) vs data in the budget is just a single monthly value by category..and I just cant figure out how to relate the two. So far this measure would show me the amount of spend by categories: SpendByCategory = SUMX(VALUES('Category Mappings'[Category]),CALCULATE(SUM('Bank Export'[Amount]))) But how do i lookup the category and pull the amount that I need to subtract from the actual spend ?
