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.
How to calculate average profit ONLY when the product has been bought and sold in selected time.
Hello. I need to calculate the average profit of per product and in general in the selected time period. It is for a scrap business that buys scrap and then sells it forward. However, I only want to calculate profits for a product that has been bought AND sold in the selected period, because otherwise it will calculate a loss if a product has been recently bought in the selected time period but not sold. Now I have created a DAX formula that can calculate this on single products. Formula looks like this: Kate keskimäärin = IF( NOT(ISBLANK([Ostot keskimäärin €/T])) && NOT(ISBLANK([Myynti keskimäärin €/T])), [Myynti keskimäärin €/T] - Ostot[Ostot keskimäärin €/T], BLANK() ) Where "Ostot keskimäärin €/T" is average buying price and "Myynti keskimäärin €/T" is average sales price. It works and calculates this on single products but when it comes to the overall average on all the products it calculates also the ones that how been bought but not sold and vice versa. As you can see in this table the total is calculated falsley since it includes everything. Does anybody know how I can get the average profit in total? It doesnt necessarily have to be visible in the table but I need the calculation to show the average profit All help will be appriciated.Display % in Matrix visual with decimal number formatting - within multiple filtered contexts v2
Hello DAXperts, I have posted a similar issue before here and that was resolved perfectly. This one is a different problem as I have different formulas in different contexts. I have a matrix visual showing data from a "MainData" table. I have been able to display % numbers for selected rows with a decimal formatted matrix. This works for the single monthly columns (measures), but does not work for the Full Year (custom measure called "Total"), and also does not work for the corresponding "Budget" full year total (also a custom measure). I want these two columns/measures ("Total" and "Budget") to show % values at selected rows consistent with the other columns in the matrix. Currently, the decimals also looks to be incorrect. Ideally, the Gross Profit % or any percentages should be divided by Revenue like the other columns. Ie. Gross Profit % = Gross Profit / Revenue Earnings Before Tax % = Earnings Before Tax / Revenue Please see below image & attached PBIX file. Any suggestions or workarounds much appreciated.Display % in a Matrix visual that has decimal number formatting - within multiple filtered contexts
Hello DAXperts, I have a matrix visual showing my data from a "MainData" table. I have been able to display % numbers for selected rows while the rest of the matrix is decimal formatted. But this only works within certain filtered contexts based on the selected slicers. Ultimately, I have a Profit & Loss report, I want this report to show the correct calculated % values for the "Gross Profit %" row. Currently, the data does contain the correct decimal values for the % equivalent number for "Gross Profit %". But the numbers are correct when only a single Business Unit is selected on the slicer. Ideally, the GP% can be calculated by use of a measure or something similar (but I have not gone this route): GP% = DIVIDE( CALCULATE( [PL], 'MainData'[L1] = "Gross Profit", CALCULATE( [PL], 'MainData'[L1] = "Revenue" ) ) The measure I really need help with is [ActualsPL w %] or [ComparisonPL w %]. See below image and attached PBIX file. Any suggestions or workarounds is much appreciated!
