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.
Self-reference with DAX for Average
Hi Everyone, I have an Issue currently I have a measure called [Rate] which will be provided for actual calculated months, what I need to avhive in the [Average] Column is if the [Rate] has a value keep the rate otherwise provide an average of the last 12 months but e.g. in the table provided Nov will be the average of Nov 23 to Nov 24 [Rates] but for December I will need to take the recently calculated rate for Nov 24 and consider it for the Avg Dec 23 to Dec 24 my main issue is power BI is not self referencing the latest calculated row inted is only taking the moving average until Nov 24. Here is the Dax I'm using: DEFINE var _RollingPeriodStart = DATE(2024,3,1) var _Start = DATE(2023,1,1) var _PreviousMonth = DATE(2025,5,31) var _LastSync = [Latest Sync Date] var _Table = SUMMARIZE( FILTER( ALL('Calendar'), 'Calendar'[Date] >= _Start && 'Calendar'[Date] <= _PreviousMonth ), 'Calendar'[Year-Month], 'Calendar'[Year Month Class], "EoMDate", LASTDATE('Calendar'[Date]), "Rate", [_Rate] * 100 ) var _Table_2 = ADDCOLUMNS( _Table, "Index", RANKX( _Table, 'Calendar'[Year Month Class], , ASC, DENSE ) ) var _Table_3 = ADDCOLUMNS( _Table_2, "Average", IF( NOT(ISBLANK([Rate])), [Rate], AVERAGEX( FILTER( _Table_2, [Index] < EARLIER([Index]) && [Index] >= (EARLIER([Index]) - 12)), [Rate]))) EVALUATE _Table_3 This what need to be achieved. Instead Power BI is doing thisForecast value measure
I'm trying to generate a measure which will track the amount of approved jobs out of all jobs forecasted for the current month. The problem In the guage visual above I currently have approved documents out of max approved (monthly) historically. This is because I have to look back in time for the denominator (i.e. max value) because jobs are generated on an ongoing basis. So I can't know at the beginning of each month how many jobs will be created. And the max value only gives an approximation of how many jobs will be created and is therefore not very accruate. The issue with using max is that it's backward-looking and as is clear from the historical time series, the # of jobs each month are growing. What I really would like is a measure which gives an estimate value of jobs to be expected for the current month which is forecasted from the count of jobs historcially approved each month. The solution would be a measure version of the forecasting available in analytics which will give a numerical value of jobs forecasted to be generated this month. Is there a way to generate this forecast with DAX code in Powerbi? Thanks in advance!
Cumulative progress chart [Model attached]
Hi, See model with test data here: https://drive.google.com/file/d/1S4G3rC5-SAothJEkQBq2DhXRtWXrM9a3/view?usp=drivesdk Outout: I want to create the following graph Test data used in model Contract scheduled done Reschedule 1 Reschedule 2 Status Contract done 2023-04-25 Done 2023-04-25 2023-05-15 Done 2023-06-15 2024-01-03 2024-03-01 2024-04-05 Delayed Status missing 2024-04-03 Progressing 2024-06-03 Delayed 2024-08-01 Progressing Variables Actual The cumulative sum of "Contract done" when the status column is "Done". It should only show up until Today's date In the above example, the cumulative sum today would be 2. Planned The cumulative sum of "Contract scheduled done" It should only show from Today's date and forward In the above example, the cumulative sum would be 2 Forecast The cumulative sum of "Reschedule 2", "Reschedule 1" and "Contract scheduled done" Logic should be: If "Reschedule 2" contains data > pick this value. If "Reschedule 2" is emtpy > look at "Reschedule 1". If "Reschedule 1" contains data > pick this value. If "Reschedule 1" is emtpy > pick the value in "Contract scheduled done" Only from Today's date and forwardPrevious Month Calculation to Forecast
Hi all, I have the below data: Month Headcount Hires Other Increase Other Decrease February 400 0 0 March 20 10 20 April 5 10 0 May 10 0 0 Problem statement: I need to create a calculation that sums up the headcount, hires, increases and decreases for future months. I want March to take February's headcount and add March's respective Hires, Other Increases and minus Other Decreases. For example March would be 400 + 20 +10 -20 = 410 April would then take March's value (410) and perform the same calculation: 410 +5 +10 - 0 = 425 May would then take Aprils number and so on... I am having trouble trying to get each month to take the calculated vale of the month before. Please help and thank you in advance.Get Power BI Forecast in a table
Hello, I'm trying to use actual sales during a pre-order moment to determine how many pieces per item should be ordered. For this I use the forecast function in the Power BI line graph visual which works really great. The only thing is, I can only see the forecasted value while hovering over it with my mouse, which makes it difficult to determine an order amount for a lot of items (about 2000). Also I cant use these values in formulas. My question is; is it possible to create (for example) a table which displays this forecast? The eventual result I want to see is the forecasted sales on a date in the future for a specific item. Here is a fictional simplified dataset that I'm trying to use. The real dataset will use about 2000 items with 98 days of sales and the to be forecasted amount of days is 168. Item Quantity Date item 1 40 1-jul item 1 55 2-jul item 1 38 3-jul item 1 59 4-jul item 1 60 5-jul item 1 59 6-jul item 1 38 7-jul item 1 59 8-jul item 1 48 9-jul item 1 58 10-jul item 1 69 11-jul item 1 48 12-jul item 1 38 13-jul item 1 32 14-jul item 1 52 15-jul item 1 62 17-jul item 1 74 17-jul item 1 43 18-jul item 1 54 19-jul item 1 34 20-jul item 1 65 21-jul item 1 37 22-jul item 1 48 23-jul item 1 61 24-jul item 1 61 25-jul item 1 48 26-jul item 1 37 27-jul item 1 71 28-jul item 1 74 29-jul item 1 82 1-aug item 1 71 3-aug item 1 61 7-aug item 1 65 8-aug item 1 36 10-aug item 1 57 11-aug item 1 86 12-aug item 1 36 13-aug item 1 26 14-aug item 1 100 15-aug item 2 3 1-jul item 2 4 2-jul item 2 5 3-jul item 2 4 4-jul item 2 5 5-jul item 2 0 6-jul item 2 0 7-jul item 2 2 8-jul item 2 0 9-jul item 2 5 10-jul item 2 6 11-jul item 2 4 12-jul item 2 0 13-jul item 2 3 14-jul item 2 2 15-jul item 2 0 16-jul item 2 3 17-jul item 2 3 18-jul item 2 2 19-jul item 2 0 20-jul item 2 4 21-jul item 2 3 22-jul item 2 5 23-jul item 2 0 24-jul item 2 0 25-jul item 2 0 26-jul item 2 6 27-jul item 2 0 28-jul item 2 0 29-jul item 2 2 30-jul item 2 0 31-jul item 2 0 1-aug item 2 0 2-aug item 2 1 3-aug item 2 0 4-aug item 2 0 5-aug item 2 0 6-aug item 2 3 7-aug
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.
Rolling Forecast by month - Forecast accuracy of the year
Hello everybody, I am new to the community and to Power BI itself, I have been looking for an answer on the already posted questions but I haven't found a solution to my needs. In my organization we receive a monthly forecast file (units) of what is going to be ordered to our main warehouse. We have then forecast set 01/01/2021, 01/02/2022 an so on. I am now working on analysing this data and its accuracy vs the actual sales (or issues from the warehouse in units). My issue at the moment is I am not able to show in Power BI the correct units I have in my source file, as for one Forecast Set (month in which I receive the forecast) there might be more than one Forecast Date (month in which the units are going to be ordered). Source file: Material Forecast Date Forecasted Units Forecast Set 40163 01/04/2021 350 01/04/2021 40163 01/06/2021 50 01/06/2021 40163 01/04/2021 350 01/07/2021 40163 01/06/2021 50 01/07/2021 As you can see above, the total Forecasted Units for 2021 should be 400 for Material 40163, as I am considering what is forecasted for the month (Forecast Date). What I am getting in Power BI is the total sum of everything visible, hence 800 units: Additional info: I have a Dates table All data for Forecast units and Sales units is coming from a consolidated Forecast table and is working for monthly calculations (Fcst Error, Abs Error, Accuracy, Bias) Relationships: The forecast source file is at this WeTransfer link: https://we.tl/t-T7AjGKBrOF The question would be what is the best DAX command to use in order to "remove those duplicates"? should I create a new forecast Column or Measure? Which in this case means having for Material 40163, just 400 units Forecasted. I apologize in advance if I wasn't clear explaining my issue, and please let me know if I can add any extra info that can be of help. I thank you for your time. Kind regards,Combining sales + forecast in one column; problems with getting the last sales date
I am trying to show a trendline in which the sales actuals line flows over in a forecast. I found a way to do this online, but get errors in the first part already when I try to obtain the last sales date. The instruction video I am using is: https://www.youtube.com/watch?v=DKgF-5QHY68. In the first part of the DAX, I need to obtain the last sales date. This formula is not working (I believe) because I have the Sales Amount and the Forecast in the same table, and thereby the last sales date is in fact, the last forecast date (see table below for example): Finance Date Sales Amount Forecast Budget 10-2021 100 150 120 11-2021 150 200 140 12-2021 0 180 150 12-2022 0 200 120 I also tried to calculate this with a simple IF statement, but although it seems in a table it is working, when I plot it in a line chart it messes up all the data. Example of that IF statement: Sales + Forecast = IF[Sales Amount] = 0, result if true [Forecast], result if false [Sales Amount]. What should I do? Should I change the DAX formula or should I change the data model and split the main table into three different tables (sales table, forecast table, budget table). Pls help, I am very lost...
